CODEWITHSUNDEEP
sql-servercsvt-sql
QMaster
QMaster

Reputation: 3904

Check if one of item in comma delimited string exist in another comma delimited string

I can check single string in comma delimited string, for example finding varchar data field that contain single value such as 1 or 2 or 5 in '1,2,3,4,5' comma delimited string as described here: https://stackoverflow.com/a/49202026/1830909, But I'm wondering how could I check if compare string isn't single solid string and is comma delimited string too. for example data field is varchar and containe comma delimited string like '1,3,4' and I want to check if one of items such as 1 or 3 or 4 is exist in '1,2,3,4,5' comma delimited string, I hope success to clarify that, Any help appreciated.

Clarifying: Although "keeping delimited strings in a column is a bad idea" but I think it's not matter when the biggest value just contain less than 15 item, in some situation I have too much table and I don't want increasing. Other reason is like to use json for transferring data, Parsing all values in one delimited string and save to one column of DB table and pool it from DB as an string and pars to different values.

Upvotes: 1

Views: 10215

Answers (1)

Alan Burstein
Alan Burstein

Reputation: 7918

You need a string splitter (AKA tokenizer). In SQL 2016+ you can use string_split pre-2016 I suggest DelimitedSplit8K. This code returns a 1 is there is a matching value, a 0 otherwise. 

DECLARE 
  @string1 varchar(100) = '1,32,2',
  @string2 varchar(100) = '1,2,3,4,5';

SELECT matchingValue = ISNULL(MAX(1),0)
FROM string_split(@string1,',')
WHERE [value] IN (SELECT [value] FROM string_split(@string2,','));

Upvotes: 4

Related Questions