Count the number of digits in a field

Question

I am using Sql Serevr 2008

How can I count the number of digits in a database field , something along these lines:

select headline,
       REGEXP_count(name,'\d') as num_count
from accountTbl
where num_count > 3;

I am trying to detect if somebody has written more then 3 numeric digits in their headline text.

For example I want to catch:

'hello call me on 3 4 5 6 7 8' - match

'this is my number 234875' - match

'4 hello 4 and 66' - match

'leaving on the 24th going to be there at 6:30' - match

Solution 1

SQL Server 2008 does not support regular expressions; only patterns are supported. You can use a query like this to find matches of three digits or more:

select headline
from accountTbl
where patindex('%[0-9]%[0-9]%[0-9]%', headline) > 0

You wouldn't get the count, but you would be able to filter. The downside to this solution is that in order to search for, say, five digits you would need to change the pattern itself.

Here is a quick demo on sqlfiddle.

Solution 2

The easiest way that I can think of how to count digits in a string in SQL Server 2008 would be to count the difference between the length of the original string and the length of it when stripped for all digit characters (0..9). Your query could look something along the lines of:

(LEN(headline) * 10) - LEN(REPLACE(headline, '0', '') + 
                           REPLACE(headline, '1', '') +
                           REPLACE(headline, '2', '') +
                           REPLACE(headline, '3', '') +
                           REPLACE(headline, '4', '') +
                           REPLACE(headline, '5', '') +
                           REPLACE(headline, '6', '') +
                           REPLACE(headline, '7', '') +
                           REPLACE(headline, '8', '') +
                           REPLACE(headline, '9', ''))

You should test if it's any faster calculating the length on each replace or concatenating strings first and then calculating the length of all of it, but both ways should be much the same in performance terms if your strings aren't exceptionally long. Another, a bit more difficult to read version that doesn't concatenate could be:

LEN(headline) - 
  LEN(REPLACE(
        REPLACE(
          REPLACE(
            REPLACE(
              REPLACE(
                 REPLACE(
                   REPLACE(
                     REPLACE(
                       REPLACE(
                         REPLACE(headline, '9', ''), 
                       '8', ''), 
                     '7', ''), 
                  '6', ''), 
                '5', ''), 
              '4', ''), 
            '3', ''), 
          '2', ''), 
        '1', ''), 
      '0', ''))

But I'm not quite sure it makes it any more visually appealing (SQL Server 2008 could really use regular expressions). Another approach worth considering, if you need a way to determine the number of digits in a string is described in this blog.

Cheers! ;)