mysql REGEXP not matching

Question

I have a regular expression intended to catch phone numbers within a string

/[1,+ ().-][\s.-]\d{3}[\s.-]\d{3}[\s.-]\d{4}/

I try to query my MySql database for this regex using this query:

SELECT
    *
FROM
    `everything`.`instances_meta` AS m
WHERE
    `meta.value` REGEXP "[1,+][\s.-]\d{3}[\s.-]\d{3}[\s.-]\d{4}"

I can confirm the regex, itself, does what I want from testing it here but it does not pull any matches when queried. Something tells me I am missing some sort of expression to say "anywhere in the string". I would specify the regex engine but I am unsure of what MySql uses.

Thanks for sharing a status report. (You are unsure what MySQL uses for a Regular Expression engine. That is documented in the MySQL Reference Manual, which is available.) But what I don't see is an actual question. — spencer7593
– spencer7593, Commented Oct 3, 2016 at 21:11

Wiktor Stribiżew · Accepted Answer · 2016-10-03 21:01:48Z

4

Use

"[1,+][[:blank:].-][0-9]{3}[[:blank:].-][0-9]{3}[[:blank:].-][0-9]{4}"

as MySQL REGEXP does not support \s and \d.

The [:blank:] matches spaces and tabs, and [0-9] matches any ASCII digit.

answered Oct 3, 2016 at 21:01

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Wiktor Stribiżew Over a year ago

FYI: MySQL REGEXP is not anchoring the pattern, and looks for a match anywhere inside the string (called a partial match). MySQL uses a POSIX regex flavor, a very limited set of regular expression patterns, however, the POSIX character classes are also fine to use. Note you might want to use [[:digit:]] instead of \d, or [:space:] instead of [:blank:] - test and see which is performing best.

Dherik Over a year ago

Where do you find this information about what REGEXP support in MySQL? I'm having the same problem and the documentation not mention these limitations.

Wiktor Stribiżew Over a year ago

@Dherik What is the MySQL version you are using? Older versions before MySql v.8 used a POSIX regex engine so you may check all POSIX regex related reference.

Dherik Over a year ago

I'm using the 5.7 version

Wiktor Stribiżew Over a year ago

So, the main part is at dev.mysql.com/doc/refman/5.7/en/regexp.html. The rest is at pubs.opengroup.org/onlinepubs/009695399/basedefs/…

Collectives™ on Stack Overflow

mysql REGEXP not matching

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related