好记性不如烂笔头。

将指定分隔符的字符串转换成表

alter FUNCTION F_StringSplitTable
(
@str NVARCHAR(MAX),
@split NVARCHAR(10)
)
RETURNS TABLE
AS
RETURN
(
SELECT B.id
FROM
(
SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@str , @split , '</v><v>') + '</v>')
) A
OUTER APPLY
(
SELECT id = N.v.value('.' , 'nvarchar(100)')
FROM A.[value].nodes('/v') N ( v )
) B
)
Go
--查询可以为
select * from tb where id IN (SELECT CAST(id AS INT) FROM dbo.F_StringSplitTable('11,22,3', ','))