CODEWITHSUNDEEP
mysqlregex
mane
mane

Reputation: 1169

Mysql regex to retrieve string with more then 6 digits in between of any string type (aphabets or non-alphabets)

I am required to retrieve only those data rows that match certain pattern, like:

[email protected]

[email protected]

Need to retrieve, strings with pattern {all_string}{digits_morethen_6}{all_strings} in MYSQL, can anyone help me with the right regex to implement in the sql script

Upvotes: 3

Views: 2413

Answers (1)

Rick James
Rick James

Reputation: 142258

WHERE col REGEXP '[0-9]{7}'

That looks for 7 digits (more than 6) anywhere in a string.

If you need the parts before and after to be non-empty, then:

WHERE col REGEXP '[^0-9][0-9]{7,}[^0-9]'

This looks for at least 7 digits, preceded and followed by a non-digit.

Edit

Examples (of second REGEXP):

mysql> SELECT 'asdf2345zxcv' REGEXP '[^0-9][0-9]{7,}[^0-9]';
+-----------------------------------------------+
| 'asdf2345zxcv' REGEXP '[^0-9][0-9]{7,}[^0-9]' |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+

mysql> SELECT 'asdf2344444445zxcv' REGEXP '[^0-9][0-9]{7,}[^0-9]';
+-----------------------------------------------------+
| 'asdf2344444445zxcv' REGEXP '[^0-9][0-9]{7,}[^0-9]' |
+-----------------------------------------------------+
|                                                   1 |
+-----------------------------------------------------+

Upvotes: 4

Related Questions