Search code examples
mysqlregexconcatenationword-boundary

mysql concat regexp word boundary and quote

Here is my query

SELECT producer FROM producers WHERE producer REGEXP CONCAT('[[:<:]]', 'dell\'', '[[:>:]]')

I replaced mysql like with this to use word boundary from another example here. But now I am having a problem with escaped apostrophe, it doesn't find the dell' in the database even if there is a match.

Solution 
  • select count(*) from (select 'dell\'' as c) t where c regexp '[[:<:]]dell\'';        -- -> 1
select count(*) from (select 'dell\'' as c) t where c regexp '[[:<:]]dell\'[[:>:]]'; -- -> 0

    So it's the trailing boundary requirement which fails. Which makes sense. Quoting from the docs:

    These markers stand for word boundaries. They match the beginning and end of words, respectively. A word is a sequence of word characters that is not preceded by or followed by word characters. A word character is an alphanumeric character in the alnum class or an underscore (_).

    As ' is not a word character, it cannot be the end of a word, hence [[:>:]] can't match.