CODEWITHSUNDEEP
mysqlsql
Pelin
Pelin

Reputation: 457

Check if a string contains number with more than 5 digits

I am trying to build a query that checks whether the string contains at least more than 5 consecutive digits or not.

Query;

SELECT count(id) as gV FROM  someTable WHERE ... title REGEXP '(\d{5,})' order by id limit 0,10

Sample data

Some text here 123456 (MATCH)
S0m3 t3xt h3r3
Some text 123 here 345
98765 Some text here (MATCH)
Some12345Text Around (MATCH)

Desired output

3 (Some text here 123456, 98765 Some text here, Some12345Text Around)

Is there any specific rules for regex in MySQL queries?

Upvotes: 2

Views: 6064

Answers (1)

Michael Berkowski
Michael Berkowski

Reputation: 270609

MySQL's regular expression engine does not implement the \d "digit" expression, but instead you can represent it either as a character class range like [0-9] or as the special character class [[:digit:]]. The curly brace repeat syntax {5,} is supported in the form you've attempted.

The available regular expression syntax is described in the manual

So you can use either of the following forms:

 title REGEXP '[0-9]{5,}'
 title REGEXP '[[:digit:]]{5,}'

Examples:

Non matching:

> SELECT '123' REGEXP '[[:digit:]]{5,}';
+--------------------------------+
| '123' REGEXP '[[:digit:]]{5,}' |
+--------------------------------+
|                              0 |
+--------------------------------+

> SELECT '1X345' REGEXP '[0-9]{5,}';
+--------------------------------+
| '123' REGEXP '[0-9]{5,}'       |
+--------------------------------+
|                              0 |
+--------------------------------+

Matching examples:

> SELECT '98765 Some text here' REGEXP '[[:digit:]]{5,}';
+-------------------------------------------------+
| '98765 Some text here' REGEXP '[[:digit:]]{5,}' |
+-------------------------------------------------+
|                                               1 |
+-------------------------------------------------+

> SELECT 'Some text here 123456' REGEXP '[0-9]{5,}';
+--------------------------------------------+
| 'Some text here 123456' REGEXP '[0-9]{5,}' |
+--------------------------------------------+
|                                          1 |
+--------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 5

Related Questions