1

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.

Improve this question
10
  • 1
    You stop using strings and convert it to separated rows. Possible duplicate of Turning a Comma Separated string into individual rows Commented Mar 9, 2018 at 21:20
  • I agree with @JuanCarlosOropeza on this one. Instead of trying to function this one to death, turn your poorly stored data (multiple items in a single field) into one record per item. Then you can use the database to do set based logic and stick this condition in the join of your two properly normalized sets. As always, sample data and desired results will go far in getting someone to help out. Commented Mar 9, 2018 at 21:22
  • @JNevill Thanks for fast answer, but this project is finished meanwhile record count in table about 300 and the biggest delimited value in each record about 5 item, so as a pragmatic solution I used this way, Otherwise I know it is poor solution. Commented Mar 9, 2018 at 21:28
  • @JuanCarlosOropeza Thanks for your heeds, But I don't want to turn it to table, I'm lookinf for solution to check that as an string. Commented Mar 9, 2018 at 21:30
  • You can split both strings into table variables and select a join, but you really should normalize your database. Otherwise you might be facing much harder problems. Commented Mar 9, 2018 at 22:04

1 Answer 1

Reset to default
4

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,','));
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

This is so interesting, I need this solution specially For SQL Server 2008 R2 It seems to not support this commands, Anyway I point this answer +1 and after ending talking with @ZoharPeled and No better answer I will accept it as an answer, Appreciate man.
Well, obviously the correct solution would be to normalize the table and avoid using delimited strings altogether. Assuming that's impossible, then this is about the only solid workaround. +1. @QMaster Assuming you can't normalize the database, you should accept this answer and work according to it.
@ZoharPeled I agree with you, Accepted, Thanks so much.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.