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.

QMaster
  • 3,743
  • 3
  • 43
  • 56
  • 1
    You stop using strings and convert it to separated rows. Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) – Juan Carlos Oropeza Mar 09 '18 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. – JNevill Mar 09 '18 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. – QMaster Mar 09 '18 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. – QMaster Mar 09 '18 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. – Zohar Peled Mar 09 '18 at 22:04
  • You dont need create a table. In the link I provide he create a cte to generate the result. Once you have the result you can proced using joins to generate the result you want. – Juan Carlos Oropeza Mar 10 '18 at 05:43
  • @JuanCarlosOropeza you right, I haven't care enough, I'll check it and will write about result. – QMaster Mar 10 '18 at 23:07
  • @ZoharPeled Thanks, I can but as I said I don't want to turn it to table even table variable. – QMaster Mar 10 '18 at 23:09
  • Well, do you have any reason not to do it the right way? keeping delimited strings in a column is a bad idea to begin with, and insisting on handling it as string is adding insult to injury as far as good practice goes. Why do you insist on using worst practice? – Zohar Peled Mar 11 '18 at 05:06
  • @ZoharPeled I agree with you about "keeping delimited strings in a column is a bad idea" but as I said before 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, Can I describe it? – QMaster Mar 13 '18 at 22:53

1 Answers1

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,','));
Alan Burstein
  • 7,770
  • 1
  • 15
  • 18
  • 1
    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. – QMaster Mar 13 '18 at 23:00
  • 1
    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. – Zohar Peled Mar 14 '18 at 05:14
  • 1
    @ZoharPeled I agree with you, Accepted, Thanks so much. – QMaster Mar 18 '18 at 09:31