How to use every word of a string in WHERE clause?

Question

I am facing a problem , I have a query in SQL Server 2014, The query result should be based on a WHERE clause that takes a string from a C# CheckedListBox.
I have the string in this form (the following values are for example only) :-
cat,dog,bird,duck
And inside the database the records look like this:-
dog cat-dog cat-dog-duck bird-dog-duck etc...

I have tried this :-

DECLARE @animals nvarchar(max) = 'dog,bird,cat'
select x,y,z WHERE CHARINDEX(animals, replace(@animals,',',' ')) > 0

The result would show rows with only ONE SINGLE VALUE like dog cat bird But it wouldn't show rows with values like dog-cat dog-cat-bird etc! it just shows rows with one single word from @animals string.

How can I select all rows where column animals contains either a word or more from @animals string. Thanks in advance...

or also `WHERE animals LIKE'%'+ replace(@animals,',',' ')+'%''` this also doesn't work — Medo, Nov 15 '18 at 07:38
hmm, of course that is not going to work...you need to pick apart the individual animals and build a where clause `LIKE '%dog%' OR '%cat%'` — jazb, Nov 15 '18 at 07:39
SQL Server has types *designed* for holding multiple values. These are, in order of preference, tables, JSON and XML. You'll notice that "comma separated string" isn't in that list. So if at all possible, change the calling code to use a more appropriate data type and avoid the mess that is T-SQL string manipulation. — Damien_The_Unbeliever, Nov 15 '18 at 07:40
ok so do i need a kind of a for loop for each word in the string? how can i achieve this? I tried cursor and I can't do it for the `WHERE` part only so i end up with multiple result sets — Medo, Nov 15 '18 at 07:42
@Damien_The_Unbeliever so you advise me to split this string and insert every word in a temp table for example? — Medo, Nov 15 '18 at 07:48
No, I'd prefer you declare a user-defined table type and so pass this data from the C# code as a table-valued parameter in the first place. You're *creating* problems by taking the information from the `CheckedListBox` and turning it into a comma-separated string in the first place. Rather than seeking better ways to undo this, don't do it at all. — Damien_The_Unbeliever, Nov 15 '18 at 07:50

score 2 · Answer 1 · edited Jun 12 '19 at 11:48

You should take a look at Table valued parameters, it will let you send in a table of values as a parameter from C# to SQL. You make a table with "animals" and then make a sub-select in your Stored Proc.

See example code from link below, shows how to pass the parameter:

 // Assumes connection is an open SqlConnection object.  
    using (connection)  
    {  
       // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  

  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  

  // Execute the command.  
  insertCommand.ExecuteNonQuery();  
    }

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

score 2 · Answer 2 · answered Nov 15 '18 at 07:55

You should create a temp table for store all searching value or you should create a temp table from the comma separated variable for the example visit Query for convert CSV values into temp table. Then use inner join for filter records from your table like below.

declare @temp table (animal varchar(50))

insert into @temp values ('cat')
insert into @temp values ('dog')
insert into @temp values ('bird')


select * from SomeTable a
inner join @temp t on a.Column like '%' + t.animal + '%'

Make stored procedure for that query and call it from C#.

Zohar Peled · Answer 3 · 2018-11-15T08:06:42.243

And inside the database the records look like this:- dog cat-dog cat-dog-duck bird-dog-duck etc...

There is the source of your problems. Before reading anything else I wrote in my answer, you should read Is storing a delimited list in a database column really that bad?, where you will see a lot of reasons why the answer to this question is Absolutely yes!.

Once you're done with that, the solution to the problem should be as obvious to you as it is to me - Fix the database structure - meaning remove that column storing delimited data and replace it with a table referenced by a many-to-many relationship to your existing table, that will hold the animals data for you.

The first part of the solution is using a table valued parameter instead of sending a delimited string to the database.
There are plenty of examples on how to do this on stackoverflow - like here and there.

Once you've done that, you can use a hack with like as a workaround, in case you can't change the database structure:

SELECT <ColumnsList>
FROM <TableName> As T
JOIN @TVP As TVP
    ON '-' + T.Animals +'-' LIKE '%-' + TPV.Animal +'-%'

Note I've added the delimiter to both ends of both columns.

If you can change the structure you will have a query like this:

SELECT <ColumnsList>
FROM <TableName> As T
JOIN TableToAnimals AS TTA 
    ON T.Id = TTA.TableId
JOIN Aniamls AS A
    ON TTA.AnimalId = A.Id
JOIN @TVP As TVP
    ON A.Name = TVP.Animal

Good answer for the "don't even do it this way" remarks. – LoztInSpace Nov 15 '18 at 08:23 — LoztInSpace, Nov 15 '18 at 08:23
Downvoting without an explanation is useless. – Zohar Peled Nov 19 '18 at 04:40 — Zohar Peled, Nov 19 '18 at 04:40

score 1 · Accepted Answer · answered Jun 12 '19 at 11:16

For anyone who has the same problem. I have found the solution for this on SQL Server.
Use the Full Text Search and your problem is easily solved. It's awesome.
Check the following link :-
https://learn.microsoft.com/en-us/sql/relational-databases/search/get-started-with-full-text-search?view=sql-server-2017

score 0 · Answer 5 · answered Nov 15 '18 at 07:57

use like this:

var query = string.Empty;
var index=0;
foreach(var animal in animals) {
    if (query.Length>0) {
        query +=" and ";
    }
    var paramName = "@animalName" + index++;
    query +="(animals like " + paramName + " or animals like '%' + " + paramName + " or animals like " + paramName + " + '%' or animals like '%' + " + paramName + " + '%')";
    SqlParameter thisParam = new SqlParameter(paramName, animal);
    command.Parameters.Add(thisParam);
}
command.CommandText = "select * from tableName WHERE " + query;

How to use every word of a string in WHERE clause?

5 Answers5