1.将字符串拆分为表:例子:“1,2,3,4,5”拆分为表
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [fnGetSplitString]
(
@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
调用 select * from fnGetSplitString('1,2,3,4,5',',')