How to write MySQL REGEXP?
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!'
b开发者_开发百科ut 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 forREGEXP
[[: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.
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.
精彩评论