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.

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?


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

CREATE Function [fnRemoveNonNumericCharacters](@strText VARCHAR(1000))
    WHILE PATINDEX('%[^0-9]%', @strText) > 0
        SET @strText = STUFF(@strText, PATINDEX('%[^0-9]%', @strText), 1, '')
    RETURN @strText
|improve this answer|||||
  • 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. – Rachel Jul 19 '12 at 16:06
  • 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. – Aaron Bertrand Jul 19 '12 at 16:27
  • @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. – Rachel Jul 19 '12 at 16:39

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))
          @n INT = 1,
          @Return VARCHAR(100) = ''

    WHILE @n <= LEN(@String)
          SET @Return = @Return + CASE
                             WHEN ASCII(SUBSTRING(@String, @n, 1)) BETWEEN ASCII('0') AND ASCII('9')
                                THEN SUBSTRING(@String, @n, 1)
                                ELSE ''
          SET @n = @n + 1

         WHEN @Return = ''
            THEN NULL
            ELSE @Return
|improve this answer|||||

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.