locked
How to replace in sql RRS feed

  • Question

  • let output string will be,

    str="01/Mar/2010,02/Mar/2010,03/Mar/2010,04/Mar/2010,05/Mar/2010"

    Some manipulation, *******

    Resultant string should be,

    "01/Mar/2010,02/Mar/2010<br/>03/Mar/2010,04/Mar/2010<br/>05/Mar/2010"

    Here I want to replace Comma (,) by <br/>

    Note:

    I want to replace even commas only. (before 03/mar/2010 & 05/Mar/2010..like)

    Thursday, June 10, 2010 5:30 AM

Answers

  • Please Check this….

     

    DECLARE @var VARCHAR(MAX) = '01/Mar/2010;02/Mar/2010;03/Mar/2010;04/Mar/2010;05/Mar/2010;06/Mar/2010;07/Mar/2010'

     

     

    DECLARE @textXML XML;

    SELECT @textXML = CAST('<d>' + REPLACE(@var, ';', '</d><d>') + '</d>' AS XML);

     

    SELECT IDENTITY(INT, 1, 1) AS ID ,LTRIM(RTRIM(T.split.value('.', 'NVARCHAR(MAX)'))) AS data

    INTO #ABCD

    FROM @textXML.nodes('/d') T (split)

         

    UPDATE #ABCD

    SET data = CASE WHEN ID <> 1 AND ID % 2 <> 0 THEN '<br>' + DATA

                            WHEN ID = 1 THEN DATA

                            ELSE ',' + DATA 

                            END

         

    DECLARE @DATA VARCHAR(MAX) = ''

     

    SELECT @DATA = @DATA + ''+ DATA

    FROM #ABCD

    WHERE ID in (select id from #abcd)

         

    SELECT @DATA

     

    DROP TABLE #ABCD

    Thanks,

    Sandeep

    Thursday, June 10, 2010 7:35 AM