How can I strip non-numeric characters out of a string?

Question

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('[phone]', '[^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?

Answer 1

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

• Rachel: My question was asking for a T-SQL alternative to using the CLR function. I only posted the extra CLR data because you asked for it in the comments, and I thought you knew of a way to fix the problem. I'd prefer to fix the CLR method, however my research has shown that the "fix" is to upgrade to a 64-bit server, which is not an available option to me at this time. I realize now that all the CLR info in the question can be misleading, so I removed it from my question entirely.
• Aaron Bertrand: I thought that maybe the method you used to deploy the assembly or create the function might yield some clue. "Escalation policy" made me think it might have something to do with security or safe/unsafe access. Sorry I can't be of more help, but on a 32-bit server you may be best off using T-SQL instead.
• Rachel: @AaronBertrand No problem, thanks for the input :) We do hope to move to a 64-bit server within the next year or two, so hopefully that should clear up the CLR error completely.

Answer 2

This works much better:

SELECT
 (SELECT CAST(CAST((
 SELECT SUBSTRING(FieldToStrip, Number, 1)
 FROM master..spt_values
 WHERE Type='p' AND Number <= LEN(FieldToStrip) AND
 SUBSTRING(FieldToStrip, Number, 1) LIKE '[0-9]' FOR XML Path(''))
 AS xml) AS varchar(MAX)))
FROM
 SourceTable

• b_levitt: just keep in mind that this doesn't work in Azure Sql instances

Answer 3

I'm pretty confident in this solution. I'm not certain about performance but any opinions about this approach are definitely welcome! Basically for each character in the string @String if the ASCII value of the character is between the ASCII values of '0' and '9' then keep it, otherwise replace it with a blank.

CREATE FUNCTION [dbo].[fnStripNonNumerics](
 @String VARCHAR(500))
RETURNS VARCHAR(1000)
AS
BEGIN
 DECLARE
 @n INT = 1,
 @Return VARCHAR(100) = ''
 WHILE @n <= LEN(@String)
 BEGIN
 SET @Return = @Return + CASE
 WHEN ASCII(SUBSTRING(@String, @n, 1)) BETWEEN ASCII('0') AND ASCII('9')
 THEN SUBSTRING(@String, @n, 1)
 ELSE ''
 END
 SET @n = @n + 1
 END
 RETURN CASE
 WHEN @Return = ''
 THEN NULL
 ELSE @Return
 END
END

Answer 4

The WHILE is not as performant as the following, which also works on Azure SQL and caters for most INTs and protects against 'CAST' failures for BIGINTs (10+ characters long)

DECLARE @MaxColumnLen INT = 30;
DECLARE @Tally TABLE (n int);
INSERT INTO @Tally (n) SELECT TOP (@MaxColumnLen) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_objects;
set @starttime=getdate();
SELECT count(1) as 'Number of valid INT fields'
FROM YourTable yt
WHERE
 TRY_CAST((
 SELECT SUBSTRING(yt.YourTextField, t.n, 1)
 FROM @Tally t
 WHERE t.n <= LEN(yt.YourTextField)
 AND SUBSTRING(yt.YourTextField, t.n, 1) LIKE '[0-9]'
 AND LEN(yt.YourTextField) <= 9 -- to avoid BIGINT overflow if the string contains a numerical value greater than '2147483647'
 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(''' + cast(@MaxColumnLen as nvarchar(12)) + ''')') AS INT
 ) IS NOT NULL
;
set @endtime=getdate(); select DATEDIFF(ms,@starttime,@endtime) as 'Time in ms';