SQL find text in string

Question

I have a text field in my database:

DECLARE @vchText varchar(max) = 
 This is a string<>Test1<>Test2<>Test

That @vchText parameter should return like this:

  This is a string: 

     1. Test1    
     2. Test2 
     3. Test

Anyone think of a good way to correct this. I was thinking the STUFF and CHARINDEX Functions with a WHILE LOOP...?

Something I should also note would be that there might not be only 1,2,3 items in the list there could be lots more so I can't build it so its static and only handles 1,2,3 it should be able to work for any number of items in the list.

Answer 1

I was able to do it with a loop and use the stuff and charindex below.

    DECLARE @vchText varchar(max) = 
 This is a string<>Test1<>Test2<>Test


DECLARE @positionofNextX INT = CHARINDEX('<>', @vchText)
DECLARE @nbrOFListItems INT = 1

WHILE @positionofNextX  != 0
BEGIN
    SET @NOTE = STUFF( @vchText, @positionofNextX, 4, CAST(@nbrOFListItems AS VARCHAR(1)) + '. ')

    SET @positionofNextX  = CHARINDEX('<>',  @vchText)

    --increment the list item number
    SET @nbrOFListItems = @nbrOFListItems + 1
END

print  @vchText

Answer 2

Try this. Break the string into parts.

1. First part - This is a list:

2. Second part - 1.Test1 1.Test2 1.Test3

Convert the second part into rows using the delimiter Space. Then add row_number to the rows. Append the row_number and column data.

Finally convert the different rows into single row delimited by space and append it with the first part

DECLARE @NOTE   VARCHAR(max) = 'This is a list: 1.Test1 1.Test2 1.Test3',
        @temp   VARCHAR(max),
        @output VARCHAR(max)

SELECT @temp = Substring(@NOTE, Charindex(':', @NOTE) + 2, Len(@note))

SELECT @output = LEFT(@NOTE, Charindex(':', @NOTE) + 1)

SELECT @output += CONVERT(VARCHAR(10), Row_number() OVER (ORDER BY col))
                  + Substring(col, Charindex('.', col), Len(col))
                  + ' '
FROM   (SELECT Split.a.value('.', 'VARCHAR(100)') col
        FROM   (SELECT Cast ('<M>' + Replace(@temp, ' ', '</M><M>') + '</M>' AS XML) AS Data) AS A
               CROSS APPLY Data.nodes ('/M') AS Split(a)) ou

SELECT @output -- This is a list: 1.Test1 2.Test2 3.Test3