Fastest way to remove non-numeric characters from a VARCHAR in SQL Server Fastest way to remove non-numeric characters from a VARCHAR in SQL Server sql-server sql-server

Fastest way to remove non-numeric characters from a VARCHAR in SQL Server


I saw this solution with T-SQL code and PATINDEX. I like it :-)

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))RETURNS VARCHAR(1000)ASBEGIN    WHILE PATINDEX('%[^0-9]%', @strText) > 0    BEGIN        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')    END    RETURN @strTextEND


replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(string,'a',''),'b',''),'c',''),'d',''),'e',''),'f',''),'g',''),'h',''),'i',''),'j',''),'k',''),'l',''),'m',''),'n',''),'o',''),'p',''),'q',''),'r',''),'s',''),'t',''),'u',''),'v',''),'w',''),'x',''),'y',''),'z',''),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z','')*1 AS string,

:)


In case you didn't want to create a function, or you needed just a single inline call in T-SQL, you could try:

set @Phone = REPLACE(REPLACE(REPLACE(REPLACE(@Phone,'(',''),' ',''),'-',''),')','')

Of course this is specific to removing phone number formatting, not a generic remove all special characters from string function.