----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