Filtering characters to just return numeric characters in a string?

Question

I want to filter an nvarchar field to return just the numeric values.

I have some SQL to do this but it seems way more complicated than it needs to be. I am interested in finding out if anyone has a better way to filter out any non-numeric characters in a string?

IF OBJECT_ID('tempdb..#MOB') IS NOT NULL
BEGIN
    DROP Table #MOB
END

SELECT [mob]
INTO #MOB
FROM (
SELECT '(00) 1234 5678' AS [mob]
UNION
SELECT '1234 5678' AS [mob]
UNION
SELECT '+61 012 345 678' AS [mob]
) AS temp


;WITH [fill] ([Num], [Index], [MOBILEPHONE])
AS
(
    SELECT 
    CASE 
        WHEN [MOBILEPHONE] IS NOT NULL
        THEN SUBSTRING([MOBILEPHONE], 1, 1) 
        ELSE NULL 
    END AS [Num]
    , 1 AS [INDEX], [MOBILEPHONE]
    FROM (
        SELECT DISTINCT [mob] AS [MOBILEPHONE]
        FROM #MOB as t
    ) AS temp
    UNION ALL
    SELECT 
    SUBSTRING([F].[MOBILEPHONE], [F].[Index] + 1, 1) AS [Num]
    ,[F].[Index] + 1 AS [Index]
    , [MOBILEPHONE]
    FROM [fill] AS [F]
    WHERE ([F].[Index] + 1) < LEN([F].[MOBILEPHONE]) + 1
)

SELECT [E].[MOBILEPHONE] AS [old_MOBILEPHONE],
    STUFF((SELECT N'' + [F].[Num]
    FROM [fill] AS [F]
    WHERE (PATINDEX('%[^0-9]%', [F].[Num]) = 0 OR PATINDEX('%[^0-9]%', [F].[Num]) IS NULL) AND
    ([F].[MOBILEPHONE] = [E].[MOBILEPHONE])
    ORDER BY [F].[MOBILEPHONE], [F].[Index]
    FOR XML PATH('')), 1, 0, '')
    AS [MOBILEPHONE]
FROM (
        SELECT DISTINCT [t].[MOBILEPHONE]
        FROM (SELECT [mob] AS [MOBILEPHONE] FROM #MOB) as t
    ) AS [E]

Output

╔═════════════════╦═════════════╗
║ old_MOBILEPHONE ║ MOBILEPHONE ║
╠═════════════════╬═════════════╣
║ (00) 1234 5678  ║  0012345678 ║
║ +61 012 345 678 ║ 61012345678 ║
║ 1234 5678       ║    12345678 ║
╚═════════════════╩═════════════╝

I have seen the Q & A T-SQL select query to remove non-numeric characters on Stack Overflow, but that answer has a similar solution to what I have found, using a CTE table and recursion. I am searching for something simpler. Hoping there is something like a custom collation that I can create or a regex filter I can apply?

Answer 1

To use regular expressions, you would need to use a SQLCLR function. Solomon Rutzky has created a library of useful CLR functions called SQLsharp. The free edition includes several regular expression functions including RegEx_Replace4k as used below:

SELECT 
    M.mob,
    numeric_only = 
        SQL#.RegEx_Replace4k
        (
            M.mob,  -- Source
            N'\D',  -- Regular expression
            N'',     -- Replace matches with empty string
            -1,     -- Unlimited replacements
            1,      -- Start at character position
            NULL    -- Options (see documentation)
        )
FROM #MOB AS M;

This produces the output shown below:

╔═════════════════╦══════════════╗
║       mob       ║ numeric_only ║
╠═════════════════╬══════════════╣
║ (00) 1234 5678  ║   0012345678 ║
║ +61 012 345 678 ║  61012345678 ║
║ 1234 5678       ║     12345678 ║
╚═════════════════╩══════════════╝

Regular expressions are a bit overkill for this simple requirement, so writing your own CLR implementation to just remove non-numerics would likely be even faster. Nevertheless, I have found the library function above to be as fast, if not faster, than the best T-SQL implementations (T-SQL string manipulation is rather slow).

For a T-SQL implementation, see Splitting Strings Based on Patterns by Dwain Camps.

Answer 2

The following "digits only" function was created by Eirikur Eiriksson and it fairly well blows the doors off of most T-SQL-Only solutions performance-wise (1 million conversions of random strings varying in length from 36 to 72 characters in just a little over 15 seconds). Additional information on the testing can be found on the following thread on SQLServerCentral.com. http://www.sqlservercentral.com/Forums/Topic1585850-391-2.aspx#bm1629360

CREATE FUNCTION dbo.DigitsOnlyEE
--Created by Eirikur Eiriksson (29 Oct 2014)
        (@pString VARCHAR(8000)) 
RETURNS TABLE WITH SCHEMABINDING AS RETURN
   WITH  E1(N)    AS (SELECT N FROM (VALUES (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL)) AS X(N))
        ,Tally(N) AS (SELECT TOP (LEN(@pString)) (ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS Num FROM E1 a,E1 b,E1 c,E1 d ORDER BY Num) 
 SELECT DigitsOnly = 
(
 SELECT SUBSTRING(@pString,N,1)
   FROM Tally 
  WHERE ((ASCII(SUBSTRING(@pString,N,1)) - 48) & 0x7FFF) < 10 
  ORDER BY N
    FOR XML PATH('')
)
;

It is an iTVF (inline Table Valued Function) so you have to use it in your FROM clause rather than in the SELECT List, like the following.

 SELECT ca.DigitsOnly
   FROM dbo.SomeTable
CROSS APPLY dbo.DigitsOnlyEE(SomeString) ca
;

I won't load SQLSharp even for testing because it appears to install a certificate and a user and so would likely never use it in production. Even if I would, I couldn't do a performance test because the author has the following restriction in his EULA, which also puts me off from installing it. I don't agree with such restrictions but I will respect them.

2.2. Limitations Apart from the rights specifically enumerated in Section 2.1, neither Licensee and its Affiliates, nor End Users shall have any other rights related to the Software. By way of illustration, and not limitation, the foregoing license does not provide any right whatsoever to:

2.2.8. use the Software or Documentation for purposes of competitive analysis of the Software, the development of a competing software product or service, or any other purpose that is to the Licensor’s commercial disadvantage.

Answer 3

See if this will work for you - it has been successful for us.

CREATE FUNCTION [dbo].[RemoveAlphaCharacters] (@Temp NVARCHAR(1000))
RETURNS NVARCHAR(1000)
AS
BEGIN
    DECLARE @KeepValues AS NVARCHAR(50)

    SET @KeepValues = '%[^0-9]%'

    WHILE PatIndex(@KeepValues, @Temp) > 0
        SET @Temp = Stuff(@Temp, PatIndex(@KeepValues, @Temp), 1, '')

    RETURN @Temp
END