locked
How to write recursive TSQL Replace query? RRS feed

  • 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.

    Maybe this is a special character problem?  This is what one of the records looks like, even after I run the query above.

       
    <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>




    Ryan D
    Thursday, October 20, 2011 8:27 PM

Answers

  • On Thu, 20 Oct 2011 20:27:01 +0000, ironryan77 wrote:

    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.

    Maybe this is a special character problem?  This is what one of the records looks like, even after I run the query above.
       

    <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>

    Hi Ryan,

    Take a look at this article. Solution #3 seems to fit in your requirements:
    http://www.projectdmx.com/tsql/strcleanup.aspx


    Plamen Ratchev
    • Marked as answer by ironryan77 Friday, October 21, 2011 3:58 PM
    Thursday, October 20, 2011 8:50 PM