Query a SQL field for whether it contains any one of multiple values

Question

I want to query the rows of SQL column for whether it contains any one of multiple values.

For instance, return rows of a table where its column A contains any of the following words: ('cow','farmer','milk').

Easy enough when you know what the words are, but I want to write a sproc where I can feed in any array of strings and if the column A for a certain row contains any of them, it would return the row.

I would want to be able to feed in:

('cow','farmer','milk')

or

('cow','farmer','steak','yikes')

or

('cow','farmer','three', 'pigs', 'wolf')

It must be relatively straightforward, but I cannot for the life of me figure it out. I'm on SQL Server 2008

Answer 1

One simple approach:

declare @candidates varchar = '|cow|farmer|three|pigs|wolf|'
select * from TableName where @candidates like '%|' + FieldName + '|%'

The best way to do this in SQL 2008 is with a table-valued parameter.

Answer 2

SQL Server doesn't know what an "array" is. You can write a split table-valued function that turns each value into a row and then joins against the base table, but this is definitely sub-optimal compared to Av's answer. Or you can test that against a table-valued parameter. A TVP is definitely the best performer compared to all of the splitting techniques, even CLR.

CREATE TYPE dbo.FarmItems AS TABLE(Item VARCHAR(32));
GO

CREATE PROCEDURE dbo.FindFarmItems
  @List dbo.FarmItems READONLY
AS
BEGIN
  SET NOCOUNT ON;

  SELECT t.col1, t.col2, ...
    FROM dbo.table AS t
    INNER JOIN @List AS L
    ON t.columnA = L.Item;
END
GO

Then in C# you just construct a DataTable with your "array" and pass it in:

DataTable dt = new DataTable();
dt.Columns.Add("Item", typeof(string));
dt.Rows.Add("cow");
dt.Rows.Add("farmer");
...

using (SqlConnection conn = new ...)
{
    SqlCommand c = new SqlCommand("dbo.FindFarmItems", conn);
    2.CommandType = CommandType.StoredProcedure;
    SqlParameter tvp = c.Parameters.AddWithValue("@List", dt);
    tvp.SqlDbType = SqlDbType.Structured;
    // execute, get a reader, etc...
}

Answer 3

I had the same dilema, and came up with the following. For example, if table_a contains ('My Cow', 'My Goat', 'My Dog') and table_b contains ('Dog', 'Cow'), then...

create table #table_a (field1 varchar(128))
insert into #table_a values('My Cow')
insert into #table_a values('My Goat')
insert into #table_a values('My Dog')

create table #table_b (field1 varchar(128))
insert into #table_b values('Dog')
insert into #table_b values('Cow')

select * from #table_a table_a where (select count(*) from #table_b table_b where charindex(table_b.field1, table_a.field1) > 0) > 0

returns ('My Cow','My Dog')

Hope this helps.

Answer 4

You can use the XML data type to pass list data to your stored procedure:

create procedure dbo.LookItUp

  @idList xml

as

  declare @lookup table
  (
    id int not null
  )

  insert @lookup (id)
  select distinct t.id.value('.','int')
  from @idList.nodes('/ids/id') as t( id )
  where t.id is not null

  select *
  from dbo.my_data_table t
  join @lookup           lu on lu.id = t.object_id

  return 0
go

Easy!

Answer 5

You can't pass arrays to Stored Procedures, as arrays don't exist in TSQL. You have a few options, I'm giving you two of them.

Option 1 - Quick and dirty (I don't recommend it)
Pass the values as a string and add it to a dynamic SQL Statement.

CREATE PROCEDURE MyProc
  @Values varchar(1000)
AS

DECLARE @SQL VARCHAR(2000)

SET @SQL = 'SELECT blahblah WHERE SomeField IN (' + @Values + ')'

-- Execute the statement and return the result

END

Apart from the obvious SQL Injection vulnerability, this approach won't work for big sets and it won't perform too well either. Also, it won't work if any value contains a comma. I really don't recommend it, although it may be useful for quick testing.

Option 2 - A more flexible solution
Store all your values in a temporary table which you will reference in your Stored Procedure.

CREATE TABLE #MyTempTable
-- Fields...

INSERT INTO #MyTempTable
-- Insert the values

CREATE PROCEDURE MyProc
  @Values varchar(1000)
AS

SELECT 
  SomeFields
FROM
  MyTable
  JOIN
  #MyTempTable ON
    -- Add join clause

END

This solution may scale better.

As other have suggested, you can also use an in-memory table (which I personally avoid, as I never had much luck with them, they always performed worse than temporary tables), or a table parameter, but you must declare its type beforehand.

Answer 6

You can simply use WHERE column IN (coma separated strings)

Here is my complete example how to do that:

create table #bar (foo varchar(20))

insert into #bar (foo) values('cow')
insert into #bar (foo) values('cat')

select foo from #bar
where foo in ('cow','farmer','milk')

drop table #bar