How to write MySQL REGEXP?

Question

A table contains the string "Hello world!"

Thinking of * as the ordinary wildcard character, how can I write a REGEXP that will evalute to true for 'W*rld!' but false for 'H*rld!' since H is part of another word. 'W*rld' should evalute to false as well because of the trailing '!'

Answer 1

If you are just looking to match the word world, then do this:

SELECT * FROM `table` WHERE `field_name` LIKE "w_rld!";

The _ allows for a single wildcard character.

Edit: I realize the OP requested this solution with REGEXP, but since the same result can be achieved without using regular expressions, I provided this as viable solution that should perform faster than a REGEXP.

Answer 2

Use:

WHERE column REGEXP 'W[[:alnum:]]+rld!'

Alternately, you can use:

WHERE column RLIKE 'W[[:alnum:]]+rld!'

• RLIKE is a synonym for REGEXP
• [[:alnum:]] will allow any alphanumeric character, [[:alnum:]]+ will allow multiples
• REGEXP \ RLIKE is not case sensitive, except when used with binary strings.

Reference: MySQL Regex Support

Answer 3

You can use regular expressions in MySQL:

SELECT 'Hello world!' REGEXP 'H[[:alnum:]]+rld!'
0
SELECT 'Hello world!' REGEXP 'w[[:alnum:]]+rld!'
1

More information about the syntax can be found here.