syntax for @var in column containing comma separated values

Question

I need the syntax for a where clause on the following table:

rec_id desc stores

1 abc '1','3'
2 def '1','2','3'
3 ghi '2','4'

declare @VAR varchar(3)
set @VAR = '''2'''

select desc from table
where @VAR found in the stores column ?????

Thanks for any help,
Marc Miller

Answer 1

Don't store values as comma-separated lists. Relational databases are designed from the idea that each cell has an atomic value. If you violate that rule, you will have to pay dearly by complicated and slow code.

Anyway, this article on my web site will give you the information you need, if you insist on keeping this format: https://www.sommarskog.se/arrays-in-sql.html.

Answer 2

If your SQL server is 2016 or higher, try this:

DECLARE @T TABLE (
    rec_id int, 
    [desc] varchar(20), 
    stores varchar(20)
);

INSERT INTO @T
SELECT 1, 'abc', '1,3' UNION
SELECT 2, 'def', '1,2,3' UNION
SELECT 3, 'ghi', '2,4';

DECLARE @var varchar(3)
SET @var = '2'

SELECT * 
FROM @T
WHERE @var IN (SELECT value FROM STRING_SPLIT(stores, ','));

Answer 3

Hi @Marc Miller ,

Here is a solution based on XML and XQuery. It will work starting from SQL Server 2012 onwards.

XML data model is based on sequences. That's exactly what we have in the stores column i.e. sequence of numbers.

SQL

-- DDL and sample data population, start  
DECLARE @tbl table (rec_id INT IDENTITY PRIMARY KEY, [desc] VARCHAR(20), stores VARCHAR(200));  
INSERT INTO @tbl ([desc], stores) VALUES  
('abc','''1'',''3'''),  
('def','''1'',''2'',''3'''),  
('ghi','''2'',''4''');  
-- DDL and sample data population, end  
  
DECLARE @separator CHAR(3) = ''','''  
 , @param INT = 2;  
  
;WITH rs AS  
(  
 SELECT *   
 , TRY_CAST('<root><r>' +   
 REPLACE(REPLACE(stores, @separator, '</r><r>'), '''', '') +   
 '</r></root>' AS XML)  
 .query('sql:variable("@param")=(/root/r/text())')  
 .value('.', 'BIT') AS result  
 FROM @tbl  
)  
SELECT * FROM rs  
WHERE rs.result = 1;  

Output

+--------+------+-------------+--------+  
| rec_id | desc |   stores    | result |  
+--------+------+-------------+--------+  
|      2 | def  | '1','2','3' |      1 |  
|      3 | ghi  | '2','4'     |      1 |  
+--------+------+-------------+--------+  

Answer 4

Hi @Marc Miller ,

Welcome to the microsoft TSQL Q&A forum!

Please refer to:
1.Use string_split(applies to SQL Server 2016 and later):

CREATE TABLE yourtable (  
     rec_id int,   
     [desc] varchar(20),   
     stores varchar(20)  
 );  

INSERT INTO yourtable VALUES(1,'abc','''1'',''3'''),(2,'def','''1'',''2'',''3'''),  
                      (3,'ghi','''2'',''4''');  

DECLARE @var varchar(3)  
SET @var ='''2'''  

SELECT * FROM (SELECT *  
FROM yourtable y  
CROSS APPLY STRING_SPLIT(y.stores, ',')) t  
WHERE [value]=@var;  

2.Create user-defined functions(applies to SQL Server (all supported versions))

    CREATE FUNCTION SplitStr(@Sourcestr VARCHAR(8000), @Seprate VARCHAR(100))       
    RETURNS @result TABLE(F1 VARCHAR(100))       
      AS         
       BEGIN       
       DECLARE @sql AS VARCHAR(100)       
      SET @Sourcestr=@Sourcestr+@Seprate         
      WHILE(@Sourcestr<>'')       
      BEGIN       
        SET @sql=left(@Sourcestr,CHARINDEX(',',@Sourcestr,1)-1)       
        INSERT @result VALUES(@sql)       
         SET @Sourcestr=STUFF(@Sourcestr,1,CHARINDEX(',',@Sourcestr,1),'')       
       END       
       RETURN    
       END  
     GO  

    DECLARE @var varchar(3)  
    SET @var ='''2'''  

    SELECT * FROM (SELECT *   
    FROM yourtable y   
    CROSS APPLY SplitStr(y.stores,',')) t  
    WHERE [F1]=@var;   

    DROP FUNCTION SplitStr  

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.