Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Can you show the code you used to add the assembly and create the function? –  Aaron Bertrand Jul 19 '12 at 15:14

2 Answers 2

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
share|improve this answer
up vote 5 down vote accepted

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
share|improve this answer
    
If you don't want a T-SQL alternative, then why are you posting this as an answer? –  Aaron Bertrand Jul 19 '12 at 15:59
    
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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.