User Comments

If you are searching for literal parentheses, you have to enclose each parenthesis in brackets; otherwise, mySQL thinks they're part of the regular expression syntax. For instance:

WHERE phone REGEXP '(435)';

would return any phone numbers that have the sequence 435 in any part of the string, such as "1(801)555-4351". However:

WHERE phone REGEXP '[(]435[)]';

would return only phone numbers with (435), such as "1(435)555-5555".

It's far beyond the scope of this documentation to dwell on all the gory details of regular expressions. Should you have any doubts, please refer to a good book on the subject like "Mastering Regular Expressions" (http://www.oreilly.com/catalog/regex/). References online include http://sitescooper.org/tao_regexps.html and http://www.regexlib.com/

The regular expression support in MySQL seems to be based on traditional-style regex (like ereg() in PHP), not the more sophisticated regular expression matching found in Perl or PHP's preg_match(). And in case the above doesn't make it clear (being mostly SELECT statements using the function directly to return a 1 or 0), you typically would use the REGEXP function in a WHERE clause like this:

SELECT * FROM foo WHERE bar REGEXP "baz"

To match a "special" character such as $, you need to prefix it with the backslash \ character. So \$ matches an actual dollar sign. However, in almost any programming language that claims a "C-like" syntax, that backslash is likely to get picked up on as a special character. So you may need to use an extra backslash. Also, at least in Perl and PHP, the $ is a special character itself, because it indicates that what follows is a variable name - so it will need a backslash too.

In Perl or PHP, you probably will write something like this to match on a line starting with a $ sign:

$query = "SELECT * FROM `foo` WHERE `bar` REGEXP \"^\\\$\""

I'll explain the special characters in that and what they mean:
backslash, speech mark = a literal speech mark
HAT sign = beginning of line
two backslashes = a literal backslash
backslash, dollar = a literal dollar sign
backslash, speech mark = a literal speech mark

Now if you print $query, it will have the value
SELECT * FROM `foo` WHERE `bar` REGEXP "^\$"
which is what you really want, and how you would type it into the mysql command line. Remember also that PHPMyAdmin expects you to put a backslash before a backslash or apostrophe. So in PHPMyAdmin you would enter
SELECT * FROM `foo` WHERE `bar` REGEXP "^\\$"

I guess if you only want to use . and .* regular expressions, you may as well stick to using LIKE with the _ and % wildcards, as that is probably a bit faster. Finally, when using regular expressions in *any* language you need to watch out, because it is very easy to write ones that will always match, and almost as easy to write ones that will never match anything. So do check!

The comment above regarding the need to enclose literal parenthesis in square brackets applies to the literal period also - that is you cannot use \. -- the slash will be ignored, and the period will match anything. Using [.] will work fine.

Michael Webb's comment is wrong; MySQL does not simply "ignore" the slash. Read the manual:

"To use a literal instance of a special character in a regular expression, precede it by two backslash (\) characters. The MySQL parser interprets one of the backslashes, and the regular expression library interprets the other."

So if you want to match a literal period, put two backslashes in front of it. This will work fine:

SELECT * FROM `bleck` WHERE `mleko` REGEXP '\\.'

It works for parentheses too:

SELECT * FROM `topok` WHERE `fazzle` REGEXP '\\(435\\)'

Regexp's are pretty complicated. If you need anything more complicated than what's shown above, a good site to learn how to use them is http://www.regular-expressions.info. The sites listed in the second comment are probably a good idea too.

Also, don't get in the habit of using character classes to escape metacharacters (like using [(] to match a literal parenthese). The open parentheses '(' and ')' have no special meaning inside a character class, but others like $ retain their meaning. Using [$] won't match the character '$', it'll still match the beginning of the string.

So escape them properly with backslashes.

So what if your PHP code has something that looks like...

mysql_query("select * from a where Name rlike '(^|//////|)example'")

...just to match a string that starts with "example" or contains the string "|example". It looks terrible and it works.

For those of you struggling to escape special characters with long sequences of backslashes (see Vaz's post), I have to ask: why bother? Why not just use the dot-character syntax mentioned on this very page? For example, I needed to find all the ID attributes in some HTML. I tried escaping single and double-quotes for about 30 seconds, then I just switched to this:

SELECT * FROM site WHERE html REGEXP "id=[[.apostrophe.][.quotation-mark.]]archives[[.apostrophe.][.quotation-mark.]]";

Ta da. No escaping issues.

For those of you struggling to escape special characters with long sequences of backslashes (see Vaz's post), I have to ask: why bother? Why not just use the dot-character syntax mentioned on this very page? For example, I needed to find all the ID attributes in some HTML. I tried escaping single and double-quotes for about 30 seconds, then I just switched to this:

SELECT * FROM site WHERE html REGEXP "id=[[.apostrophe.][.quotation-mark.]]archives[[.apostrophe.][.quotation-mark.]]";

Ta da. No escaping issues.

This sql statements:

SELECT 'WORD' REGEXP '[[:upper:]]{4}'; # => 1;
SELECT 'WORD' REGEXP '[[:lower:]]{4}'; # => 0
work right only when collate is _cs and NOT _ci (case insensitive)

created tables eg. the collate 'latin1_swedish_ci' have to be changed, if you want to use case sensitive REGEXPs like [[:upper:]] or [[:lower:]]!
ALTER TABLE <name> CONVERT TO CHARACTER SET latin1 COLLATE latin_general_cs

I set in my.cnf now:
[mysqld]
default-collation= latin1_general_cs

#default was latin1_swedish_ci

The above post by Guido Dieterich (about collation and case sensitivity) is a good point. However, there is a way to match in a case-sensitive manner without having to change the collation of your existing tables: use the "BINARY" keyword.

Here's an extended example (based on the one previously posted):

SELECT
('WORD' REGEXP '[[:upper:]]{4}') AS `upper_match`, # this will be a 1
('WORD' REGEXP '[[:lower:]]{4}') AS `lower_match`, # this will be a 1 on an "*_ci" collation
# -BINARY- matches below
(BINARY 'WORD' REGEXP '[[:upper:]]{4}') AS `bin_upper_match`, # this will be a 1
(BINARY 'WORD' REGEXP '[[:lower:]]{4}') AS `bin_lower_match` # this will be a 0 even on an "*_ci" collation

The query "SELECT * FROM table WHERE text REGEXP 'UPPER'" on a *.ci (e.g. latin1_general_ci) table will find any case insensetive words, even words like "upper" or "uPpOr", or "UpPOr", etc...

To avoid this use one of the following Methods:

SELECT * FROM table WHERE text COLLATE latin1_general_cs REGEXP '...'

OR

SELECT * FROM table WHERE CAST(x AS BINARY) REGEXP '...'

Add your own comment.