mkumaran.net

How to use PostgreSQL wildcards practically

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.

Actual problem

%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.

medicine listed below

SQL query

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%'

sql query with simple wildcard

strpos() in PostgreSQL

strpos() is simple string position function in PostgreSQL

strpos( fullText, searchableString)

strpos('high', 'ig') → 2

strpos( lower(m.name), lower('junior'))

SQL query with strpos()

We can use strpos to get where exactly the matching string is.

query with strpos

using strpos() in orderby

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'))

strpos in orderby

After applied the strpos()

result after strpos


Personal website and blog by M Kumaran
twittergithub