question

MarcMiller-7626 avatar image
0 Votes"
MarcMiller-7626 asked EchoLiu-msft commented

syntax for @var in column containing comma separated values

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

sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

What is your SQL server version?

0 Votes 0 ·

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)

0 Votes 0 ·

In simple cases ‘where charindex(@var, stores) > 0’ will work too. Do you have special values, like @var = ''',''' or @var = ''''''''?


0 Votes 0 ·

Do you have anyupdate?
Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
0 Votes"
GuoxiongYuan-7218 answered

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, ','));
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @MarcMiller-7626,

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 |
 +--------+------+-------------+--------+
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @MarcMiller-7626,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.