Hi,

Here is a new approach for splitting a delimited string. It is set-based, without CHARINDEX, using number table…

See:

DECLARE @s-2 VARCHAR(620)='item_01,item_02,item_03';

SET @s-2 = ','+ @s-2 + ',';

;WITH C AS

(SELECT n, ROW_NUMBER() OVER(ORDER BY n) AS rn

FROM Nums --Number Table

WHERE SUBSTRING(@s, n, 1) = ','

AND n <= LEN(@s))

SELECT SUBSTRING(@s, A.n + 1, B.n - 1 - A.n) AS item

FROM C A

INNER JOIN C B

ON A.rn + 1 = B.rn

AND B.n - A.n > 1;

Also here is the simplified version of my technique for removing duplicate side-by-side characters.

DECLARE @result VARCHAR(8000) = '',

@string VARCHAR(8000) = 'SQQQQQQQQQQLLLLLLL Serrrrrrrrveerr';

SELECT @result = @result + Data

FROM (SELECT ID,

Data,

ROW_NUMBER() OVER (PARTITION BY Data ORDER BY ID) - ID

FROM (SELECT SUBSTRING(@String, n, 1), n

FROM Nums

WHERE n <= LEN(@String)

) D(data, ID)

) D(ID, Data, RowNum)

GROUP BY Data, RowNum

ORDER BY MIN(ID)

SELECT @result AS Result