I'm trying to modify an ILIKE match to a ~* match in PostgreSQL and I'm hitting a wall. The objective is matching a field to another field using the second one as a substring of the first one. This is the original query:
select a.col1,b.col2 from a,b where a.col1 ilike ('%' || b.col2 || '%')
Which is fine but I need to add something like a space or beginning of string clause to avoid too many matches. But, when I try:
select a.col1,b.col2 from a,b where a.col1 ~* ('^| ' || b.col2 || '$| ')
I get the error 'invalid regular expression: parentheses () not balanced'. I assume I'm either doing something wrong in the query, or that there are characters in the b field that need escaping. I don't think it's really practical to start cleaning that field up if indeed that's the issue.
Is there a way to specify that the field should be read literally, similar to the 'r' prefix for strings in Python? Or am I overlooking another issue?