CODEWITHSUNDEEP
mysqlregex
JDoc
JDoc

Reputation: 41

REGEX for MySQL Query alpha-numeric values with special characters

What is the best way for me to search this type of value via regular expression in MySQL?

"ABCDE/+19876543210@abc-def"

ABCDE/ represents a specific value that does not change

@abc-def also represents specific value that does not change

My unsuccessful attempt below:

SELECT BLAH
FROM BLOOP
WHERE (bloop.field REGEXP'^\\ABCDE/+1(123|234|345|456)[1-9]{7}@abc-def$')

Upvotes: 1

Views: 88

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562270

The + is a regexp metacharacter, so you have to escape it if you want to match a literal +. And because \ is a string metacharacter, you have to escape it too.

mysql> select 'ABCDE/+19876543210@abc-def' regexp 
  '^ABCDE/\\+1(123|234|345|456)[0-9]{7}@abc-def$' as is_match;
+----------+
| is_match |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

mysql> select 'ABCDE/+12346543210@abc-def' regexp 
  '^ABCDE/\\+1(123|234|345|456)[0-9]{7}@abc-def$' as is_match;
+----------+
| is_match |
+----------+
|        1 |
+----------+

I don't know why you had \\ in your regexp pattern, because that doesn't match the sample string.

I'm not sure what your digit triples are about. Are they phone area codes? Are you planning to list every area code?

Upvotes: 0

Code Maniac
Code Maniac

Reputation: 37755

You didn't escaped +. it has a special meaning in regex which means one or more time.

You can use this one

^ABCDE\/\\+1(123|234|345|456)[1-9]{7}@abc-def$

Demo

Upvotes: 1

Related Questions