T-SQL使用逗號分隔字串當作WHERE IN條件

 參考來源

--

CREATE FUNCTION [dbo].[SplitCsv]

(

    @csvString nvarchar(2048),

    @delimiter nchar(1)

)

RETURNS @values TABLE (value nvarchar(2048))

AS

BEGIN

DECLARE @x XML

SET @x = CONVERT(XML, 

    '<n>' + replace(@csvString, @delimiter, '</n><n>') + '</n>')

INSERT INTO @values

SELECT T.n.value('.','varchar(2048)') FROM @x.nodes('n') T(n)

RETURN

END

 

--使用範例

SELECT * FROM HumanResources.Department

WHERE DepartmentID IN (

    SELECT value FROM dbo.SplitCsv('1,3,5,7', ',')

)