TallyCTE for CSV Values

DECLARE @MfrCodes VARCHAR(8000)


SELECT @MfrCodes = '1DEF,ABS,ACI,ACL,AEA' ;



SELECT @MfrCodes = ',' + @MfrCodes + ',' ;









WITH cteTally

AS (

SELECT ROW_NUMBER() OVER ( ORDER BY o.id ) AS N

FROM master.sys.sysobjects o

CROSS JOIN master.sys.sysobjects o2

),







cteSplit

AS

(

SELECT SUBSTRING(@MfrCodes, N + 1,

CHARINDEX(',', @MfrCodes, N + 1) - N - 1) AS VALUE,N,CHARINDEX(',', @MfrCodes, N + 1) - N - 1 AS VALUE2

FROM cteTally

WHERE N < LEN(@MfrCodes)

AND SUBSTRING(@MfrCodes, N, 1) = ','

)

SELECT *,@MfrCodes

FROM cteSplit