match number of digits in string after a space

Question

I'm trying to match number of digits in a string. Here is part of my MySql query

    SELECT digits, concat(digits, ' Digits ') as selects, count(*) as count
                    FROM (SELECT (case WHEN `no` REGEXP '[[:<:]][0-9]{1}[[:>:]]' then '1'
                                       WHEN `no` REGEXP '[[:<:]][0-9]{2}[[:>:]]' then '2'
                                       WHEN `no` REGEXP '[[:<:]][0-9]{3}[[:>:]]' then '3'
                                       WHEN `no` REGEXP '[[:<:]][0-9]{4}[[:>:]]' then '4'     
                                  end) AS digits
                          FROM `no_ads` AS a 
.......
.......

problem is in a string like this 4U 2 or 4U 2 a my query will count this as 2 digits when it should count as 1 ([[:<:]][0-9]{1}[[:>:]]). How do i start counting after the 1st space?

The `[[:<:]][0-9]{2}[[:>:]]` pattern cannot match anything in `4U 2 a`. I believe the regex is not to blame here. — Wiktor Stribiżew, Feb 25 '16 at 08:58
using this http://stackoverflow.com/questions/986826/how-to-do-a-regular-expression-replace-in-mysql you could replace everything except digits and then do a string length — riteshtch, Feb 25 '16 at 09:14

Kuzeko · Accepted Answer · 2016-02-26T14:40:54.837

Your query will match the number of digits in the first word made of only digits, you can see the reduced test case here. So your query is actually working if you assume you have ONLY one of such digits, and you can see your example there in the test set.

SELECT t, (case 
     WHEN `t` REGEXP '[[:<:]][0-9]{1}[[:>:]]' then '1'
     WHEN `t` REGEXP '[[:<:]][0-9]{2}[[:>:]]' then '2'
     WHEN `t` REGEXP '[[:<:]][0-9]{3}[[:>:]]' then '3'
     WHEN `t` REGEXP '[[:<:]][0-9]{4}[[:>:]]' then '4'     
     end) AS digits
FROM test;

+---------------+--------+
| t             | digits |
+---------------+--------+
| 23            | 2      |
| 4U 2          | 1      |
| 4U 2 a        | 1      |
| 4U 23 a       | 2      |
| Ad 34 34 34 d | 2      |
| 4U 2 23 a     | 1      |
+---------------+--------+

Anyway, if you want, in general to match what happens after the first space in a line, you can use ^([[:alnum:]]+[[:blank:]]) where ^ is the symbol for the start of the string.

See here the full list of Syntax of Regular Expressions

match number of digits in string after a space

1 Answers1