I have a table like below, called abc_table:

Now, based on above data, I am looking for the name which has tag 1 and 4 / 1 or 4.

I tried using LIKE, in SQL operator, but it is not returning the expected output; I also tried with REGEX but that didn't work for me.

SELECT 
    ad.name, ad.tags 
FROM
    abc_table ad
    AND CONCAT(',', ad.tags, ',') IN (',1,4,')

This will row1 data but not the row 4 data as 98 is in between the 1 and 4

CodePudding user response：

One idea would be to use the LIKE operator and check all possible cases for each value, e.g.:

• the tag is at the beginning and the tags column contains only one element (tags = '4')
• the tag is at the beginning and the tags column contains further elements (tags LIKE '4,%')
• the tag is in the middle and the tags column contains elements before and after (tags LIKE '%,4,%')
• the tag is at the end and the tags column contains elements before it (tags LIKE '%,4')

Apply this for each tag value (1, 4) and combine the results correspondingly (if you want 1 and 4 => intersection and if 1 or 4 => union) and you should get the necessary result.

CodePudding user response：

You can do like this to get tags that has (1 and 4) / (1 or 4)

SELECT name, tags
FROM abc_table
CROSS APPLY STRING_SPLIT(Tags, ',')
where value in (1,4)
group by name, tags;

Result :

name    tags
abc     1,4,5
xyz     1,44,9
demo    1,98,4

STRING_SPLIT to Split comma-separated value string.

CROSS APPLY to transforms each list of tags and joins them with the original row

If you want to get tags that contains (1 and 4) OR (4 and 1) you can do it as follows :

SELECT name, tags
FROM abc_table
CROSS APPLY STRING_SPLIT(Tags, ',')
where value in (1,4)
group by name, tags
having count(*) = 2

result :

name    tags
abc     1,4,5
demo    1,98,4

demo here