SQL query to search into field with comma-separator

Question

I have a table with such a structure, in SQL Server 2012 (simplified for exhibit):

Id  |  Val
----+----------
1   |  1, 2
2   |  1, 3
3   |  1
4   |  2, 3, 4

I have a stored procedure with a @valIds list which contains all the Val I'd like to request, for example 2, 3 or 3.

In the first case, I'd like lines 1, 2 and 4 to be returned because they contains 2 or 3.
In the second case, I'd like lines 2 and 4 to be returned because they contains 3

Here is a fiddle to reproduce the structure : http://sqlfiddle.com/#!18/ff399/2

I can't figure out how to split the Val field to search into it and get the above results. Can anyone help me ?

Please note : Spearator is Comma-Space, not just Comma, I'm not sure STRING_SPLIT can be used here

Thank you

I really suggest you fix your design. *Never* store delimited data in your database. — Thom A, Jan 05 '22 at 16:45
Sure, but this is not the point here, and this can't be changed for a bunch of reasons I can't explain here. Unfortunately, I'm not the designer of the DB and I have to deal with it — user2687153, Jan 05 '22 at 16:48
@AaronBertrand You're right, my bad, i've fixed it in my question — user2687153, Jan 05 '22 at 16:55
*"but this is not the point here"* it kind of is, I'm afraid. If you didn't have a denormalised design, you wouldn't be asking the question. — Thom A, Jan 05 '22 at 17:11
The space doesn't make STRING_SPLIT unusable, you can replace it or trim (or try to convert the values, like I did in [my now irrelevant answer](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=05c2d4d158abb5757b1a69fe45e6bc3a)). But the fact you don't use a supported version of SQL Server does make STRING_SPLIT unusable. I've adjusted the tags on your question to indicate the version; this is a much more useful place to put that information than in the text. — Aaron Bertrand, Jan 05 '22 at 17:12

LukStorms · Answer 1 · 2022-01-05T18:05:05.053

1

You could use LIKE or CHARINDEX or PATINDEX to check if a value can be found in the string.

Example:

select *
from ACTIVITY
where concat(' ',Val,',') like concat('% ', 2,',%') 
   or charindex(' 3,', ' '+Val+',') > 0

or

SELECT *
FROM ACTIVITY t
WHERE EXISTS (
  SELECT 1
  FROM (VALUES (2),(3)) q(num)
  WHERE ' '+t.Val+',' like CONCAT('% ',num,',%')
);

edited Jan 05 '22 at 18:05

answered Jan 05 '22 at 17:04

LukStorms

28,916
5
31
45

CHARINDEX is definitely faster than splitting, but gets cumbersome to build (basically _requires_ dynamic SQL) if there can be more than a couple of elements in the list. – Aaron Bertrand Jan 05 '22 at 17:05
Well, since the OP is using such an old almost unsupported version, anything that works will be nice. – LukStorms Jan 05 '22 at 17:08
Ah, missed 2012 in the text. The fiddle was set to 2017. – Aaron Bertrand Jan 05 '22 at 17:09

Jayvee · Answer 2 · 2022-01-05T17:45:44.527

another option could be Charindex:

Declare @valIds varchar(max) = '2,3'

select distinct a.* from activity a
cross join
(Select value from String_split(@valIds,',')) as v
where charindex
(',' + value + ',' ,
 ',' + replace(val,' ','') + ',') > 0

http://sqlfiddle.com/#!18/ff399/42

For older versions of SQL server without string_split, changed the values to table using xml:

Declare @valIds varchar(max) = '2,3'

;WITH cte AS
(
   SELECT 
    
      CAST('<XMLRoot><RowData>' + REPLACE(t.val,',','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) AS x
      FROM (SELECT @valIds) AS t(val)
),
table_values as
(
SELECT 
    m.n.value('.[1]','varchar(8000)') value
FROM cte
CROSS APPLY x.nodes('/XMLRoot/RowData')m(n)
)

select distinct a.* from activity a
cross join
(Select value from table_values) as v
where charindex
(',' + value + ',' ,
 ',' + replace(val,' ','') + ',') > 0

http://sqlfiddle.com/#!18/ff399/50

csv to table credit to this answer: Convert comma delimited string to table or array in sql server 2008 without using dbo.split

@user2687153 Are you using SQL Server 2012? If so, `STRING_SPLIT` will not work for you. You'll [need a different function](https://sqlblog.org/split). — Aaron Bertrand, Jan 05 '22 at 17:18
Missed the 2012 bit too! Added another method replacing the string_split by a csv to table using xml — Jayvee, Jan 05 '22 at 17:48

score 0 · Answer 3 · answered Jan 05 '22 at 21:42

This is how to do it with "regular" XML splitting

DECLARE     @valIds VARCHAR(50) = '3, 4';

WITH s1 AS (
            SELECT      a.Id, b.x.value('.', 'INT') AS Val
            FROM        (
                        SELECT  *, CONVERT(XML, '<x>' + REPLACE(Val, ',', '</x><x>') + '</x>') AS XmlVal
                        FROM    Activity
                        ) a
            CROSS APPLY a.XmlVal.nodes('x') AS b(x)
            )
, s2 AS     (
            SELECT      b.x.value('.', 'INT') AS Val
            FROM        (
                        SELECT  CONVERT(XML, '<x>' + REPLACE(@valIds, ',', '</x><x>') + '</x>') AS XmlVal
                        ) a
            CROSS APPLY a.XmlVal.nodes('x') AS b(x)
            )
SELECT      *
FROM        Activity
WHERE       Id IN ( SELECT      s1.Id
                    FROM        s1
                    JOIN        s2
                    ON          s1.Val = s2.Val)

In s1 column Val is split the way your data should have been normalized in first place.

In s2 the parameter @valIds is split into integers.

Joining s1 and s2 on Val brings the Ids from the original Activity table which are matching the search terms.

SQL query to search into field with comma-separator

3 Answers3