Dynamic Like Statement in SQL

Question

I've been racking my brain on how to do this for a while, and i know that some genius on this site will have the answer. Basically i'm trying to do this:

SELECT column 
  FROM table 
 WHERE [table].[column] LIKE string1 
       OR [table].[column] LIKE string2 
       OR [table].[column] LIKE string3...

for a list of search strings stored in a column of a table. Obviously I can't do a like statement for each string by hand because i want the table to be dynamic.

Any suggestions would be great. :D

EDIT:

I'm using MSSQL :(

which database are you using? Is the number of Strings constant? — dsr, Jan 06 '11 at 06:21
is `table` dynamic, or a table containing column with string1/string2/string3 or what are you looking for in what? — , Jan 06 '11 at 06:27
Oh maybe the column name is known but you don't know how many 'like' comparison you need until execution time? Well in this case see my answer anyway as it works for that too (if you are using MSSQL of course) — user44298, Jan 06 '11 at 06:33

score 26 · Accepted Answer · answered Jan 06 '11 at 09:16

26

Put the parameters (string1, string2, string3...) into a table (Params) then JOIN to the table using LIKE the JOIN clause e.g.

SELECT column 
  FROM table AS T1
       INNER JOIN Params AS P1
          ON T1.column LIKE '%' + P1.param + '%';

answered Jan 06 '11 at 09:16

onedaywhen

55,269
12
100
138

1

oh wow. How could i not have thought of using an Inner join in this way? Thanks so much. :D – Derwent Jan 07 '11 at 05:53
what if `AND` operation would be desired instead of `OR`? – IndustProg Mar 10 '22 at 14:51
@GntS I think you are alluding to relational division a.k.a. "the supplier who supplies all parts": https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/divided-we-stand-the-sql-of-relational-division/ – onedaywhen Jul 01 '22 at 07:29

score 1 · Answer 2 · answered Jan 06 '11 at 09:23

Make a sample Table_1:

id  Name
1   Fred
2   Joe
3   Frederick
4   Joseph
5   Kenneth

To find all the Freds and Jos you code

SELECT 
    *
FROM
    Table_1
WHERE 
    name like 'Fred%' OR
    name like 'Jo%'

What you'd like is a dynamic WHERE. You can achieve this by putting the wildcards in Table_2:

id  Search
1   Fred%
2   Jo%

and performing the LIKE with an INNER JOIN:

SELECT
    *
FROM
    Table_1 INNER JOIN Table_2
        ON Table_1.name LIKE Table_2.search

Result:

id  Name    id  Search
1   Fred    1   Fred%
3   Frederick   1   Fred%
2   Joe 2   Jo%
4   Joseph  2   Jo%

score 0 · Answer 3 · 2011-01-06T09:55:53.363

0

Sounds like you are trying to do a fulltext search. MySQL has a tool for this:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

SELECT column FROM table MATCH (column) AGAINST ('string1 string2 string3' IN BOOLEAN MODE)

In case of MSSQL some information is available at
http://msdn.microsoft.com/en-us/library/cc879300.aspx

SELECT column FROM table WHERE CONTAINS (column , 'string1 string2 string3');

edited Jan 06 '11 at 09:55

answered Jan 06 '11 at 06:21

Yes, I'm trying to do a full-text search with multuple words, but i'm using MSSQL. I could modify that MySQL solution to do what i want to do, but is there a MSSQL version of the match function? – Derwent Jan 06 '11 at 07:42
Yeah it's here on the second link in the answer - this i at the top of it http://msdn.microsoft.com/en-us/library/ms142538.aspx – user44298 Jan 06 '11 at 08:24
Cool, This is really getting there. Thanks a lot for your help. I can do what i want to do with a WHERE CONTAINS (column, 'searchstring1' OR 'searchstring2' OR 'searchstring3' ) but what i want is something that generates the appropriate search from a separate table of search strings. we're talking about 5 or 6 search terms that include wild-card characters and other weird things. Is there some way i could generate a statement equivalent to 'searchstring1' OR 'searcstring2' OR 'searchstring3' ... for each item in a list of searchstrings? – Derwent Jan 07 '11 at 04:46

Dynamic Like Statement in SQL

3 Answers3

Linked