January 24, 2021
This is article is not about how to use wildcards in PostgreSql, instead of how to use wildcard usefully!
There are lots of good articles about how to use wildcard in PostgreSql. How to query with PostgreSQL wildcards like a Pro
This article is talking about some issues while using the wildcard and how to solve that.
%search-string% this is what I normally use to query the DB while search something. But, that’s not a usefull result because it returns the matching string wherever appears in the column.
But we need the first match at top. See the below image to understand what’s the real issue.
The same issue using SQL query. Below SQL query shows how to get the matching rows.
Here I used
%junior% as wildcard pattern matching string. But the result is not as expected.
select m.name from medicine m where m.name ilike '%junior%'
strpos() is simple string position function in PostgreSQL
strpos( fullText, searchableString) strpos('high', 'ig') → 2 strpos( lower(m.name), lower('junior'))
We can use strpos to get where exactly the matching string is.
So, we can use strpos in order by to get what we need.
select m.name from medicine m where m.name ilike '%junior%' order by strpos( lower(m."name"), lower('junior'))