SQL query using both LIKE and IN with wild cards

Question

I have a database table where users can filter a single column using a large number of variables and currently my sql query strings look something like:

SELECT * 
FROM TABLE1
WHERE COLUMN1 LIKE '%APPLE%'
AND COLUMN1 LIKE '%ORANGE%'
AND COLUMN1 LIKE '%GRAPE%'
AND COLUMN1 LIKE '%LEMON%'
AND COLUMN1 LIKE '%LIME%'
AND COLUMN1 LIKE '%TOMATO%'
AND COLUMN1 LIKE '%POTATO%'
...

which becomes a pain to implement especially if I am using a parameterized query string in a table adapter to create fill and get methods that end up having 20 parameters.

Is there any better way of doing this (like using the IN command so I only need one parameter)?

Similar to http://stackoverflow.com/questions/1387612/how-can-i-introduce-multiple-conditions-in-like-operator - @BillKarwin has a good answer there (http://stackoverflow.com/a/1387797/1073107) that I've used to do something similar. — dash, Oct 11 '12 at 21:30
@Tony - you have to be more specific: "users can filter a single column using a large number of variables" - which variables ? please provide a few examples for a valid input as well — Nir Alfasi, Oct 11 '12 at 21:30
What are you actually trying to do here? If you're searching a column for words, you may want to consider a Fulltext Index. — JamieSee, Oct 11 '12 at 21:33
@JamieSee How could I have forgotten! Of course, CONTAINS with a FullText Index would be very useful here: http://msdn.microsoft.com/en-us/library/ms187787.aspx — dash, Oct 11 '12 at 21:36
@JamieSee Okay, suppose the table is a list of all produce that I sell, and it includes entries like green apple, red apple, blood orange, vine tomato... etc and I want to filter ALL the oranges, apples, tomatoes. — Tony, Oct 11 '12 at 21:37
`SELECT * FROM Table1 WHERE CONTAINS(Column1, ' Oranges OR Apples OR Tomatoes')` would bring back all the columns that contain any of those words — dash, Oct 11 '12 at 21:39

Robert · Answer 1 · 2012-10-11T21:52:39.213

2

Maybe regular expressions will help you:

SELECT * 
FROM TABLE1
WHERE COLUMN1 LIKE '%[a-z]%'

If you use sql-server try CONTAINS

SELECT * 
FROM TABLE1
WHERE CONTAINS(COLUMN1, 'APPLE AND ORANGE')

Here you can find more information.

edited Oct 11 '12 at 21:52

answered Oct 11 '12 at 21:25

Robert

25,425
8
67
81

That's not equivalent: his query will catch alpha-numeric values, for example, while your regex won't. – Nir Alfasi Oct 11 '12 at 21:29

score 1 · Accepted Answer · answered Oct 11 '12 at 21:42

1

If this is Microsoft SQL server you should look at Full-Text Search (SQL Server) or Full-Text Search (SQL Server) if you're using SQL 2012. There are a number of ways to do searches for words more effectively than LIKE matching.

answered Oct 11 '12 at 21:42

JamieSee

12,696
2
31
47

SQL query using both LIKE and IN with wild cards

2 Answers2