MSSQL将字符串转换成列表格式

SET NOCOUNT ON

DECLARE @SCDH VARCHAR(200),@BDBH VARCHAR(200)
DECLARE @T TABLE(SCDH VARCHAR(20))

SET @BDBH='HS18080045/HS18080047'
SET @SCDH=@BDBH



--方法1
PRINT '方法1'

IF  CHARINDEX('/',@SCDH)=0 
BEGIN
	INSERT @T SELECT LEFT(@SCDH,20)
END
ELSE
BEGIN
	WHILE CHARINDEX('/',@SCDH)>0
	BEGIN
		INSERT @T SELECT LEFT(@SCDH,CHARINDEX('/',@SCDH)-1)
		SET @SCDH=SUBSTRING(@SCDH,CHARINDEX('/',@SCDH)+1,LEN(@SCDH))	
	END
	INSERT @T SELECT LEFT(@SCDH,20)
END


SELECT * FROM @T
--------------------------------------------------------------------
DELETE FROM @T
--方法2（适用于MSSQL2005及以上版本）
PRINT '方法2'
--DECLARE @BDBH VARCHAR(200)

--SET @BDBH='HS18080045/HS18080047'

INSERT @T
select 
    b.SCDH     --4.查出text值
from 
    (select [SCDH]=convert(xml,'<root><v>'+replace(@BDBH,'/','</v><v>')+'</v></root>'))a    
               --1.将逗号用xml标签替换，然后转换成xml格式
outer apply    --2.做关联查询
    (select [SCDH]=C.v.value('.','varchar(100)') from a.[SCDH].nodes('/root/v')C(v))b
			   --3.将xml内容按照标签替换拆分节点并查出每个节点的value值标记为text	
    
SELECT * FROM @T




/*
方法1
SCDH
--------------------
HS18080045
HS18080047
*/

/*
方法2
SCDH
--------------------
HS18080045
HS18080047
*/