Search text with and without special characters in a column

I am running the below query:

select *
from db.table
where text ilike '%driver needs car%'

​x
 
select *from db.tablewhere text ilike '%driver needs car%'​

I am returning the results with the exact given words 'This cool driver needs car now' but I want to include also special character if there are exist like this 'This cool driver, needs: car now'. Is it possible to search also for these special characters ?

Answer

You can use REGEXP_REPLACE to remove all non-alphanumeric characters.

select *
from db.table
where regexp_replace(text, '[^a-zA-Z0-9]', '') ilike '%driver needs car%'

 
select *from db.tablewhere regexp_replace(text, '[^a-zA-Z0-9]', '') ilike '%driver needs car%'​

Advertisement

Answer