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