How to parse a comma-delimited string variable in SQL

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?

You'll have to watch for SQL injection for sure. See this answer stackoverflow.com/questions/2944511/… — Matt
– Matt, Commented Dec 18, 2014 at 22:30

M.Ali · Accepted Answer · 2014-12-18 22:58:23Z

4

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

answered Dec 18, 2014 at 22:58

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dave.Gugg · Accepted Answer · 2014-12-18 22:33:41Z

3

You can use dynamic sql for this. Use the replace function to get the correct NOT IN() argument:

DECLARE @test VARCHAR(10) = 'a,b,c,d'

DECLARE @sql NVARCHAR(MAX)

SELECT @sql = 'SELECT *
FROM #temp
WHERE label NOT IN (''' + REPLACE( @test ,',',''',''') + ''')'

EXEC sp_executesql @sql

answered Dec 18, 2014 at 22:33

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Comments

awh112 · Accepted Answer · 2018-06-13 20:08:43Z

0

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

edited Jun 13, 2018 at 20:08

awh112

1,4824 gold badges22 silver badges35 bronze badges

answered Jun 13, 2018 at 17:28

Rahul Sur

1

Collectives™ on Stack Overflow

How to parse a comma-delimited string variable in SQL

3 Answers 3

Split Function

Test Data

Result

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Split Function

Test Data

Result

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related