Treat a whole "word" literally in a MySQL regular expression?

Question

I want to do a REGEXP match with MySQL against a variable, like so:

SELECT * 
  FROM table 
 WHERE table.CONTENT 
REGEXP CONCAT('([[:space:]]|[[:punct:]])', table.NAME, '([[:space:]]|[[:punct:]])') 

This works fine, but it's possible for table.NAME to have regexp special characters in it (e.g. '|'), in which case it gets all screwed up. Is there a regexp operator to treat an entire sequence of characters literally and ignore operators within it?

For example, if table.NAME was 'left|right' for one row, I would want it to only match if table.CONTENT literally has the string 'left|right' in it. But unless I can force that somehow, MySQL would see that as an operator and look for either 'left' or 'right'.

Answer 1

Your string should have the pipe character backslash-escaped for use in the regexp. I don't see any mysql function specifically for escaping regexp strings, but you could try using REPLACE to add the backslash:

REPLACE( table.NAME, '|', '\|')