MSSQL segment segmentation update

--> Segment update
--> Author: wufeng4552
--> Date: 2009-10-07 08:13:41
-- For example, update 'a, a, a, A'. The result of section 3 a as 'test' is 'a, a, a, test,'
Declare @ s varchar (20) -- Updated string
Declare @ split varchar (10) -- delimiter
Declare @ splitlen int
Declare @ pos int -- the updated segment is the third segment as above
Declare @ value varchar (10) -- Updated value 'test'
Declare @ I int, @ J int -- Variable
Select @ s = 'a, a, A', @ split = ',', @ splitlen = len (@ split + 'A')-2, @ I = 1, @ j = charindex (@ split, @ s + @ split), @ pos = 3, @ value = 'test'
-- Start from the beginning
While @ pos> 0 and @ I <= @ J
Begin
Select @ pos = @ pos-1, @ I = @ J + @ splitlen + 1, @ J = charindex (@ split, @ s + @ split, @ I)
End
Select @ s = case when @ I <@ J then stuff (@ s, @ I, @ J-@ I, @ value)
When @ J> len (@ s) then @ s + @ value
When @ I = @ J then stuff (@ s, @ I, 0, @ value)
Else @ s end
Select @ s
/*
--------------------
A, test,
(A data column is affected)
*/