hellouk

以CTE遞迴對字串進行split

if object_id('tempdb..#TB') is not null
   drop table #TB

-- create date
SELECT * INTO #TB FROM (
select '1' AS ID,',A,B,C,' AS CODE
UNION ALL
SELECT '2' AS ID,',B,C,D,' AS CODE
UNION ALL
SELECT '3' AS ID,'F,,D,C' AS CODE
UNION ALL 
SELECT '4' AS ID,',,K,J,' AS CODE
)X


--split using CTE
;WITH B (ID,CODE,SBUCODE) AS
(
	SELECT ID,
		CASE  WHEN CHARINDEX(',', CODE) > 0 
		THEN rtrim(ltrim(SUBSTRING(CODE,1,CHARINDEX(',', CODE)-1)))
		ELSE rtrim(ltrim(SUBSTRING(CODE,1,CHARINDEX(',', CODE)))) END AS  CODE
		,rtrim(ltrim(SUBSTRING(CODE,CHARINDEX(',', CODE)+1,LEN(CODE)))) AS  SBUCODE
	FROM #TB
UNION ALL
	SELECT A.ID,
		CASE WHEN CHARINDEX(',', SBUCODE)>0 THEN rtrim(ltrim(SUBSTRING(SBUCODE,1,CHARINDEX(',', SBUCODE)-1))) ELSE SBUCODE END CDOE,
		CASE WHEN CHARINDEX(',', SBUCODE)>0 THEN rtrim(ltrim(SUBSTRING(SBUCODE,CHARINDEX(',', SBUCODE)+1,LEN(SBUCODE)))) ELSE '' END SBUCODE
	FROM #TB A
		INNER JOIN B 
		ON A.ID=B.ID AND SBUCODE<>'' 
)
SELECT * FROM  B  WHERE CODE <>'' ORDER BY ID