Question: I am working in SQL Server 2008. I have a stored proc that takes a parameter, called @test. This parameter is varchar(255). In this stored proc, I need to parse this string, convert each value into a string itself (there will be a variable number of values), and build a list to use in a NOT IN statement.

For example, suppose @test = 'a, b, c, d'. I need to send this parameter into my stored proc. There is a SELECT query in my stored proc that uses a NOT IN statement. For this example, I need this NOT IN statement to read NOT IN('a', 'b', 'c', 'd').

How do I accomplish this? Or, is this a bad practice?

Answer: 
Use Split function something along with NOT EXISTS operator almost always faster than NOT IN operator 

Split Function
--------------

```
CREATE FUNCTION [dbo].[split]
    (
      @delimited NVARCHAR(MAX),
      @delimiter NVARCHAR(100)
    ) 
 RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
  DECLARE @xml XML
  SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

  INSERT INTO @t(val)
  SELECT  r.value('.','varchar(MAX)') as item
  FROM  @xml.nodes('/t') as records(r)
  RETURN
END

```

Test Data
---------

```
DECLARE @Table TABLE (Vals INT)
INSERT INTO @Table VALUES (1), (2), (3), (4)

DECLARE @test VARCHAR(256) = '3,4,5,6'

SELECT * FROM @Table 
WHERE NOT EXISTS (SELECT 1
                  FROM [dbo].[split](@test , ',')
                  WHERE val = Vals)

```

Result
------

```
Vals
  1 
  2

```
you can find the char or string in the whole string having delimiter as comma (,)

```
DECLARE @code VARCHAR(50) = 'c', @text varchar(100) = 'a,b,c,d,e'

SELECT CHARINDEX(',' + @code + ',', ',' + @text + ',') > 0                             

```