Match the number of digits in a line after a space

Question

I am trying to match the number of digits in a string. Here is part of my MySql request

    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 
.......
.......

the problem is in a line like this 4U 2or 4U 2 a, my request will consider this as 2 digits if it should be considered 1 ( [[:<:]][0-9]{1}[[:>:]]). How to start counting after the 1st space?

Answer 1

Your request will correspond to the number of digits in the first one word, consisting of only digits, you can see the test example given here. So your query really works if you assume that you have ONLY one of these numbers, and you can see your example there in the test case.

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      |
+---------------+--------+

, , , , , ^([[:alnum:]]+[[:blank:]]), ^ .

.