SQL query to search into field with comma-separator-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

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

CodePudding user response：

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.