Ok... making the assumption that ID1 is actually supposed to be unique, here's the creation/population of the test table that I reformatted the way I normally would write such code as an example (it's just a habit).
--===== Create and populate the test table.
-- This is NOT a part of the solution.
-- We're just setting up test data here
DROP TABLE IF EXISTS dbo.Strhold --Makes reruns in SSMS easier
CREATE TABLE dbo.Strhold (id1 INT, Bstr VARCHAR(20))
INSERT INTO dbo.Strhold
,(7,'4,12,13,14,17') --Started off as 6 for ID1 and was possible typo on part of OP
Here's one solution... the explanation is in the comments.
--===== Define what we're looking for.
-- This could be a parameter for a stored procedure or iTVF.
DECLARE @mdstr VARCHAR(20);
SET @mdstr ='4,12,13,14,17'
--===== Split all the "gazintas" to create an inline NVP table, do the comparison,
-- filter to only what match, use GROUP BY to renormalize the data
-- through aggregation, and display it all for demonstration purposes.
-- Comment out the columns you don't want/need for final code.
-- NOTE THAT THIS RELIES ON THE ID1 COLUMN CONTAINING ONLY UNIQUE DATA!!!
-- Note also that the given expected results are actually incorrect. 😉
,LookFor = @mdstr
,Bstr = MAX(sh.Bstr)
,MatchCount = COUNT(*)
,MatchValues = STRING_AGG(split.value,',')
FROM dbo.Strhold sh
CROSS APPLY STRING_SPLIT(sh.Bstr,',') split
CROSS APPLY STRING_SPLIT(@mdstr,',') find
WHERE split.value = find.value
GROUP BY sh.id1
HAVING COUNT(*) > 1 --2 or more matches
Here are the results, which also indicate that your posted desired results are actually missing the row where ID1 = 3, which actually does have 2 matches.
If ID1 actually DOES have two rows with the value of "6", post back because there's a workaround for that.
is pronounced "ree-bar
" and is a "Modenism
" for R
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)