SQL Server – How to Strip Non-Numeric Characters Out of a String

sql serversql-server-2005t-sql

Users enter a search term in a box, and that value gets passed to a stored procedure and checked against a few different fields in the database. These fields are not always of the same data type.

One field (phone number) consists of all numbers, so when checking it strips out all non-numeric characters from the string using a .Net CLR function.

SELECT dbo.RegexReplace('(123)123-4567', '[^0-9]', '')

The problem is, this function abruptly stops working on occasion with the following error:

Msg 6533, Level 16, State 49, Line 2
AppDomain MyDBName.dbo[runtime].1575 was unloaded by escalation policy to ensure the 
consistency of your application. Out of memory happened while accessing a critical resource. 
System.Threading.ThreadAbortException: Exception of type 
'System.Threading.ThreadAbortException' was thrown.
System.Threading.ThreadAbortException:

I've tried the suggestions posted on MSDN for this error, but am still getting the problem. At this time, switching to a 64-bit server is not an option for us.

I know restarting the server releases whatever memory it has held, but that is not a viable solution in a production environment.

Is there a way to strip non-numeric characters out of a string in SQL Server 2005 using T-SQL only?

Best Answer

I found this T-SQL function on SO that works to remove non-numeric characters from a string.

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