6

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?

8

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.

4

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.

  • @MikaelEriksson, I'm pretty sure that additional ORDER BYs that you added are not necessary. Please demonstrate where the code breaks by not having them. – Jeff Moden Aug 29 '17 at 16:30
  • 1
    What caught my eye was the missing order by for the concatenation part. I would not presume that an xquery execution plan behaves nicely. Order by for the top was brought to my attention by Paul and confirmed by Itzik in this blog about using top in his query generating a numbers table. m.sqlmag.com/sql-server/virtual-auxiliary-table-numbers – Mikael Eriksson Aug 29 '17 at 16:46
  • 2
    Oddly enough, whenever I use a physical Tally table, I do, in fact, use an Order By because it is "no cost" (as Paul said) and doesn't show up in the execution plan. I use it there because I did see it go astray once (but can't remember what the circumstances were). That being said, I absolutely agree that it's worth the guarantee against future upgrade problems, wonky joins, etc. I'd still be interested in seeing if anyone has actually ever had an "out of order" problem other than when TOP was in the wrong place or when the Row_Number() limit was incorrectly placed in the WHERE clause. – Jeff Moden Aug 29 '17 at 23:33
  • @JeffModen If it can be presented in a form suitable for Q & A format, you could ask that as a new question. – Paul White Aug 31 '17 at 6:49
  • @paul-white Zounds... sorry I missed your comment by almost a year, Paul. I was just wondering if anyone on this thread had seen such a thing. It's probably not worth a question. – Jeff Moden Jul 18 '18 at 19:50
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.