Dynamic Like Statement in SQL

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 + '%';

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%

Related