Sqlserver 将字符串拆分为表

----Sqlserver 将字符串拆分为表
Create FUNCTION [dbo].[fnSplitStringListXML]
(
	@strId      NVARCHAR(MAX),
	@Delimiter  CHAR(1)
)
RETURNS @TableList TABLE(strId NVARCHAR(MAX))

BEGIN
	IF @strId = ''
	    RETURN
	
	DECLARE @XML XML
	SET @XML = '<root><csv>' + REPLACE(@strId, @Delimiter, '</csv><csv>') +
	    '</csv></root>' 
	
	INSERT @TableList
	SELECT RTRIM(
	           LTRIM(REPLACE(Word.value('.', 'nvarchar(max)'), CHAR(10), ''))
	       ) AS ListMember
	FROM   @XML.nodes('/root/csv') AS WordList(Word)
	
	RETURN
END

 使用实例

FROM [Lefttb] cp
LEFT JOIN dbo.fnSplitStringListXML('sss,aaa,ccc',',') dt ON cp=dt.strId