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 '!'
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 multiplesREGEXP \ RLIKE is not case sensitive, except when used with binary strings. Reference: MySQL Regex Support
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.
REGEXP or LIKE?
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.
