How to write recursive TSQL replace query?

Question

I am using SSMS 2008 and am trying to write a recursive replace statement. I have a good start on this, but it is not working fully yet. I want to replace every occurrence of XML tags occurring in one column with empty string. So I want to replace the whole range from "<" to ">" for each record. Here is what I have:

DECLARE @I INTEGER
SET @I = 3
while 
@I > 0
--(select [note_text] from #TEMP_PN where [note_text] LIKE '%<%') 
BEGIN
UPDATE #TEMP_PN 
SET [note_text] = replace([note_text],substring([note_text],CHARINDEX('<',[note_text]),CHARINDEX('>',[note_text])),'')
from #TEMP_PN
where [note_text] LIKE '%Microsoft-com%'
SET @I = @I - 1 
END

SELECT * FROM #TEMP_PN

The problem with this code is I hardcoded @I to be 3. However, I want to make it continue replacing from "<" to ">" with empty string for each record until there are no more "<" chars. So I tried the commented out line above but this gives me an error on more than one record / subquery. How can I achieve this recursive functionality? Also, my Replace statement above only replaced "<" chars for some records, strangely enough.

I tried your sample code, but it still does not replace all instances of this text per record and for some records it does not replace any text although there is "<" in these records. Here is a record where your script does not replace any substrings. Maybe this is a special character problem?

<DIV class=gc-message-sms-row><SPAN class=gc-message-sms-from>TLS: </SPAN><SPAN class=gc-message-sms-text>Hi Reggie... I'm on my way to Lynn.. see you soon</SPAN> <SPAN class=gc-message-sms-time>3:09 PM </SPAN></DIV>

Michael Fredrickson · Accepted Answer · 2011-10-20 22:39:22Z

3

You were pretty close... the problem is that the SUBSTRING's third parameter is a length, not the position to stop at.

DECLARE @RowsUpdated INT
SET @RowsUpdated = 1
WHILE (@RowsUpdated > 0)
BEGIN
    UPDATE #TEMP_PN 
SET [note_text] = 
    REPLACE(
            [note_text], 
            substring(
                [note_text],
                CHARINDEX('<',[note_text]), 
                CHARINDEX(
                    '>',
                    SUBSTRING([note_text], CHARINDEX('<',[note_text]), 1 + LEN([note_text]) - CHARINDEX('<',[note_text]))
                )
            ),
        '')
from #TEMP_PN
where [note_text] LIKE '%Microsoft-com%' and [note_text] like '%<%>%'

    SET @RowsUpdated = @@ROWCOUNT
END

SELECT * FROM #TEMP_PN

SECOND EDIT:

OK, I've updated both queries; this code should now handle the leading > before the first tag... which I think could have been the issue.

DECLARE @TestString VARCHAR(MAX)
SELECT @TestString = '><DIV class=gc-message-sms-row><SPAN class=gc-message-sms-from>TLS: </SPAN><SPAN class=gc-message-sms-text>Hi Reggie... I''m on my way to Lynn.. see you soon</SPAN> <SPAN class=gc-message-sms-time>3:09 PM </SPAN></DIV>'

DECLARE @RowsUpdated INT
SET @RowsUpdated = 1

WHILE (@RowsUpdated > 0)
BEGIN

    SELECT
        @TestString = 
            REPLACE(
                @TestString, 
                substring(
                    @TestString,
                    CHARINDEX('<',@TestString), 
                    CHARINDEX(
                        '>',
                        SUBSTRING(@TestString, CHARINDEX('<',@TestString), 1 + LEN(@TestString) - CHARINDEX('<',@TestString))
                    )
                ),
            '')
    WHERE @TestString LIKE '%<%>%'

    SET @RowsUpdated = @@ROWCOUNT
END

SELECT @TestString

Could it be because that note doesn't meet the [note_text] LIKE '%Microsoft-com%' criteria?

edited Oct 20, 2011 at 22:39

answered Oct 20, 2011 at 19:56

Michael Fredrickson

37.5k6 gold badges95 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

salvationishere Over a year ago

Actually it looks like I still have a problem. Your code works for most records. However, I just got the error: "Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated."

Michael Fredrickson Over a year ago

Ah, I think I know when that could happen... it would happen in cases where the string was something like "> blah blah blah <tag>" So in that case it still matches the like criteria because there is a tag, but fails because the length ends up being negative because the > comes before the <.... Let me see if I can get a fix for that...

salvationishere Over a year ago

Awesome! That seems to work for me. Only issue now is that it takes awhile to run since it has to do this for 90,000 records!

Collectives™ on Stack Overflow

How to write recursive TSQL replace query?

1 Answer 1

SECOND EDIT:

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SECOND EDIT:

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related