SQL replace each occurrence of text with 1,2,3,4...etc

Vote count:

0

I have a text field in my database that is not displaying a numbered list correctly:

  DECLARE @NOTE varchar(max) = 'This is a list: 1.Test1 1.Test2 1.Test3'

Each list item has a '1.' next to it but it really should be 1. 2. 3. like this:

  'This is a list: 

     1. Test1    
     2. Test2 
     3. Test3'

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

I can get the position of the '1.' in the variable like so:

     CHARINDEX('1.', @encounterNote)
     CHARINDEX('1.',@encounterNote,CHARINDEX('1.', @encounterNote) + 1)
     CHARINDEX('1.', @encounterNote,CHARINDEX('1.',@encounterNote,CHARINDEX('1.', @encounterNote) + 1))

not sure how I can replace the second and third 1. with 2. and 3 and so on.

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 wrok for any number of items in the list.

asked 43 secs ago

BS123

448

SQL replace each occurrence of text with 1,2,3,4...etc