how to write Regular Expression in MySQL select queries?

Question

I tried this expression /\b(word\w*)\b/i to compare a word against the list of other words to find the duplicates. I used preg_math_all() and it worked fine. I wanted to do the same thing but this time check against the words retrieved from mysql database. This is what i wrote

SELECT * FROM table WHERE word = /\b(word\w*)\b/i

this didnt worked.

Answer 1

Your query should look like:

SELECT * FROM table WHERE column REGEXP '/\b(word\w*)\b/i'

Answer 2

MySQL regex syntax is a different from PHP's, you don't need the delimiters at the start and end.

SELECT * FROM table WHERE word REGEXP '\b(word\w*)\b'

You can't use the i flag for case insensitivity the same way as in PHP, as in MySQL whether the regex is case sensitive or not depends on whether the column collation is a case sensitive one or not.