Wednesday, May 25, 2016

Super fast MSSQL remove non-numeric characters from phone number

I modified the original query to use a numbers table I created so I don't have to rely on 
Microsofts master..spt_values table.

SELECT   
 (SELECT CAST(CAST(
    (SELECT SUBSTRING(PhoneNumber, Number, 1)        
     FROM dbo.Numbers        
     WHERE Number <= LEN(PhoneNumber) 
        AND SUBSTRING(PhoneNumber, Number, 1) LIKE '[0-9]' FOR XML Path('')) 
   AS xml) AS varchar(MAX)))
FROM YourContactTable

I found this method on http://programcsharp.com/blog/post/strip-non-numeric-characters-from-a-string-in-sql-server