13
SELECT * FROM abc WHERE column1 IN (a1,b1,c1)

I want to use LIKE with this select query; how can I write LIKE statement with IN, similar to the query below:

SELECT * FROM abc WHERE column1 LIKE IN (a%,b%,c%)
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
user1580770
  • 139
  • 1
  • 1
  • 5

4 Answers4

12

You can't combine like with in. Write it as separate comparisons:

select column1
from abc
where column1 like 'a%' or column1 like 'b%' or column1 like 'c%'
Guffa
  • 687,336
  • 108
  • 737
  • 1,005
6

You can't. Write it as:

column1 LIKE 'a%' OR column1 LIKE 'b%' OR column1 LIKE 'c%'
Ariel
  • 25,995
  • 5
  • 59
  • 69
2

As the other folks say you can use a list of OR conditions to specify the conditions.

You can also use a temporary table or subquery in the from clause. Here is an example of the subquery in the from clause:

select column1
   from abc
   , table(
       (select 'a%' as term from SYSIBM.SYSDUMMY1)
       union all
       (select 'b%' from SYSIBM.SYSDUMMY1)
       union all
       (select 'c%' from SYSIBM.SYSDUMMY1)
   ) search_list
   where abc.column1  like search_list.term;
Justin Swanhart
  • 1,826
  • 13
  • 15
  • I'm not a DB2 expert. This subquery in the from clause is not ANSI syntax. – Justin Swanhart Aug 07 '12 at 04:15
  • In DB2 it would probably be something like `… FROM abc, (SELECT 'a%' AS term FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 'b%' FROM SYSIBM.SYSDUMMY1 UNION ALL SELECT 'c%' FROM SYSIBM.SYSDUMMY1) search_list WHERE …` – Andriy M Aug 07 '12 at 07:02
  • edited with SYSIBM.SYSDUMMY1 (I guess that is the equivalent to FROM DUAL in MySQL or Oracle) – Justin Swanhart Aug 07 '12 at 07:05
  • I'm not sure if `table` is needed in this context. I mean I'm no DB2 expert either, but `table()` (even if it is supported by DB2) seems to be used on scalar values to "turn" them into rows, whereas `SELECT` already returns you a row set. – Andriy M Aug 07 '12 at 07:14
  • I dunno. I saw it used here (http://www.cs.newpaltz.edu/~pletcha/DB/db2_TempTables.html) and copied it :) I'm a MySQL and Oracle (occasionally MS SQL) guy. – Justin Swanhart Aug 07 '12 at 07:28
  • I see, thanks, seems justified now. And +1 on the answer, by the way. I realise that presently the query seems a handful to type, but with the `VALUES` constructor (when/if it becomes available for use in the context of a derived table in DB2) it may look more elegant. – Andriy M Aug 07 '12 at 08:44
0

If you really want to look for strings starting with a, b or c, you may opt for :

SELECT * 
FROM abc
WHERE LEFT(column1, 1) IN ('a', 'b', 'c')

I assume your case is actually more complex, but maybe you can adapt this snippet to your needs...

Cédric Bourgeois
  • 186
  • 1
  • 14