How to comma separator string use in Where clause in SQL

Using below function you can create table with valid value from comma separator string. And then INNER JOIN on your table.


CREATE FUNCTION [dbo].[fn_split] (
@value VARCHAR(MAX)
, @Delimeter CHAR(1)
)
RETURNS @SplitData TABLE (Data VARCHAR(50))
AS
BEGIN
DECLARE @XML XML

SELECT @XML = '' + REPLACE(@value, @Delimeter, '') + ''

INSERT INTO @SplitData
SELECT x.v.value('.', 'VARCHAR(50)') AS Data
FROM @XML.nodes('IDs/ID') x(v)

RETURN
END