REGEX for MySQL Query alpha-numeric values with special characters

Question

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$')

Answer 1

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

Answer 2

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?