How to pass the multiple search patterns to sql statement using WHERE IN clause

Question

Am having the table with below data,

  +----------+---------+
  | Country  | Product |
  +----------+---------+
  | Poland   | Lyca    |
  | USA      | Lyca    |
  | UK       | GT      |
  | Spain    | GT      |
  | Swiss    | Lyca    |
  | Portugal | GT      |
  +----------+---------+

From the above table, I am trying to fetch the data using the query which is given below,

 Select Country,Product from tab where Country in ('%pai%','%U%')

Query was executing but i am getting the empty resuls. So, kindly confirm me, whether the above query is valid or not.

Your `IN` condition will compare the country value to the listed string, that the reason of your empty result (the `%` character is used as substitution only for `LIKE` condition) — Joël Salamin, Sep 23 '14 at 13:29
possible duplicate of http://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server — Aramillo, Sep 23 '14 at 13:39

score 3 · Answer 1 · answered Sep 23 '14 at 13:26

3

Use like and or:

Select Country,Product
from tab
where Country like '%pai%' or
      Country like '%U%';

answered Sep 23 '14 at 13:26

Gordon Linoff

1,242,037
58
646
786

I don't see another solution to get the expected result... +1 – Joël Salamin Sep 23 '14 at 13:30

score 0 · Answer 2 · answered Sep 23 '14 at 13:52

Get your patterns into a separate table:

PATTERNTABLE
SessionKey     Pattern  
----------     -------
abc123         %pai%
abc123         %U%

and then join to it:

SELECT Country, Product
FROM Tab t
INNER JOIN PatternTable pt ON pt.SessionKey= @SessionKey 
     and t.Country LIKE pt.Pattern

The trick is in how that pattern table is populated.

How to pass the multiple search patterns to sql statement using WHERE IN clause

2 Answers2