SQL regex () don't seem to work

not sure if i'm doing something wrong. but right now this SQL regex matching works for me

url ilike '%youtube.com%'

but the moment i change it to

url ilike '%(youtube.com)%'

it stops working. not being able to group is a bit of a handicap..


Are you querying a Postgres database? If yes, then I recommend you try the ~ operator which supports pattern matching with POSIX regular expressions. For example:

url ~ '(youtube\.com|vimeo\.com)' -- case sensitive
url ~* '(youtube\.com|vimeo\.com)' -- case insensitive

ILIKE is a case-insensitive version of LIKE which is a SQL specific type of pattern matching and does not always support common regular expression notation. You can read more about ~, regexp_match (if you want to extract group values), ILIKE and other matching functions in the postgres docs.

Hope this helps!

Thanks Jane. It’s a SQL query of JSON, that’s probably why () didn’t work?
~ operator didn’t as well.