trembling trembling - 2 months ago 9
MySQL Question

mysql REGEXP not matching

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.

Answer

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.