I want to filter columns that match a specific word or sentence. For instance, if I chose the word anti
for filtering, among the following rows:
this is anti-pattern
antimosquitos products
the word anti is cool
I just want to keep the last one, since it's the only one that matches the entire matching pattern as a whole, and not within another word.
I have tried using RLIKE
and I always get a FALSE
value but I don't understand why. I expected to have a TRUE
value at least in one of these SQL statements:
SELECT RLIKE ('Spray antimosquitos', '\bantimosquitos\b', 'i') as p1;
SELECT RLIKE ('Spray antimosquitos', '\santimosquitos\s', 'i') as p1;
SELECT RLIKE ('Spray antimosquitos', '\santimosquitos', 'i') as p1;
SELECT RLIKE ('Spray antimosquitos', '\santimosquitos', 'i') as p1;
However, this is not the case. I have tried with other forms like:
SELECT RLIKE ('Spray antimosquitos', '\b antimosquitos \b', 'i') as p1
SELECT RLIKE ('Spray antimosquitos', '*antimosquitos*', 'i') as p1
SELECT RLIKE ('Spray antimosquitos', ' antimosquitos ', 'i') as p1
And I don't manage to have a returned value TRUE.
What is wrong with my RLIKE?
You need to
\t
for tab, \n
for a line feed, etc.)RLIKE
works. It is usually enabled by adding .*
on both ends of the pattern.So, in your case, you can use
'.*\\bantimosquitos\\b.*'
The RLIKE function returns TRUE only if the regular expression matches the entire string:
SELECT RLIKE('Spray anti mosquitos', 'anti') as p1; -- Returns FALSE because it doesn't match the whole string
SELECT RLIKE('Spray anti mosquitos', '.* anti .*'); -- Returns TRUE
If you want a TRUE result on a partial match, you can use REGEXP_INSTR and see if the result is >0. If so, that will return TRUE:
SELECT REGEXP_INSTR('Spray anti mosquitos', 'anti', 1, 1, 0, 'i') > 0 as p1; -- Returns TRUE
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With