MS SQL, see if any items in one list match items in another list

Question

I am writing a SQL Stored Procedure. One of the parameters that is being passed to the Stored Procedure is a list (comma-separated string).

I know if I want to find a specific string in that list I can use something like SELECT * FROM myTable WHERE myField IN (@myList)

But what if "myField" also contains a list (comma-separated string). How can I check to see if "@myList" matches any value in the list in "myField"?

I am using SQL Server 2014 SP1, if that makes any difference.

This is not an "exact duplicate of an existing question". While a suggested solution may be applicable, the linked question refers to passing data from a C# application whereas mine is about dealing specifically within a stored procedure. — eat-sleep-code, Jun 21 '16 at 17:59
you can pass a table to a stored procedure from any language including another stored procedure. In fact a stored procedure is the fastest. Using a parser as described in this answer is very slow and the wrong way to solve this problem. When your system is to slow and you are posting questions on how to speed it up remember this comment and change to using a table valued parameter. Good luck. — Hogan, Jun 21 '16 at 18:40

John Cappelletti · Accepted Answer · 2016-06-21T17:42:42.637

1

With the help of a parser... there are many out there.

Select * 
 From  myTable
 Where MyField in ( Select Key_Value from [dbo].[udf-Str-Parse](@myList,',') )

Or could be a join

Select A.* 
 From  myTable A
 Join ( Select * from [dbo].[udf-Str-Parse](@myList,',') b
   on A.MyField  = B.Key_Value

My Parser

CREATE FUNCTION [dbo].[udf-Str-Parse] (@String varchar(max),@delimeter varchar(10))
--Usage: Select * from [dbo].[udf-Str-Parse]('Dog,Cat,House,Car',',')
--       Select * from [dbo].[udf-Str-Parse]('John Cappelletti was here',' ')
--       Select * from [dbo].[udf-Str-Parse]('id26,id46|id658,id967','|')

Returns @ReturnTable Table (Key_PS int IDENTITY(1,1) NOT NULL , Key_Value varchar(max))

As

Begin
   Declare @intPos int,@SubStr varchar(max)
   Set @IntPos = CharIndex(@delimeter, @String)
   Set @String = Replace(@String,@delimeter+@delimeter,@delimeter)
   While @IntPos > 0
      Begin
         Set @SubStr = Substring(@String, 0, @IntPos)
         Insert into @ReturnTable (Key_Value) values (@SubStr)
         Set @String = Replace(@String, @SubStr + @delimeter, '')
         Set @IntPos = CharIndex(@delimeter, @String)
      End
   Insert into @ReturnTable (Key_Value) values (@String)
   Return 
End

edited Jun 21 '16 at 17:42

answered Jun 21 '16 at 17:31

John Cappelletti

79,615
7
44
66

This doesn't appear to have any different behavior than SELECT * FROM myTable WHERE myField IN (@myList). It is still looking to see if the list contained in myField the parsed list. I need to see if any value in the parsed list is contained anywhere in the myField list. – eat-sleep-code Jun 21 '16 at 17:44
Just saw where MyField may have a comma delimited string. You could use like or even charindex – John Cappelletti Jun 21 '16 at 17:44
Any idea on how to do the LIKE syntax with that. I can't wrap my head around that. I tried ... (Solutions LIKE (SELECT ('%'+Key_Value+'%') AS KeyValue FROM [dbo].[udf-Str-Parse](@Solutions,',')) OR @Solutions = '') but that blows up because multiple items are returned. – eat-sleep-code Jun 21 '16 at 17:50
1

In the ON clause on A.MyField Like '%'+B.Key_Value+'%' – John Cappelletti Jun 21 '16 at 17:54
1

I should add you may want to Select Distinct to avoid duplicates – John Cappelletti Jun 21 '16 at 17:55

MS SQL, see if any items in one list match items in another list

1 Answers1