CODEWITHSUNDEEP
db2
max092012
max092012

Reputation: 407

Combine IN and LIKE function in DB2

Is there a way to combine IN and LIKE function together in DB2? For example I would like to exclude users that have userid A,B,C and also userid that start from X% or Y% . I tried the below query however it did not work

select * from table where userid not in ('A','B','C') or (not like 'X%' or not like 'Y%')

Upvotes: 0

Views: 63

Answers (2)

Satya
Satya

Reputation: 583

Use 'AND' instead of 'OR'

select * from table 
where userid not in ('A','B','C') 
and userid not like 'X%' 
and userid not like 'Y%'

Upvotes: 1

Mark Barinstein
Mark Barinstein

Reputation: 12314

You may use all the constants used in IN in LIKE:

with 
  table (userid) as 
(
values 'A', 'AA', 'XX', 'YY', 'ZZ'
)
, vals (userid) as 
(
values 'A', 'B', 'C', 'X%', 'Y%'
)
select * 
from table t
where not exists
(
select 1
from vals v
where t.userid like v.userid
);

The result is:

|USERID|
|------|
|AA    |
|ZZ    |

Upvotes: 0

Related Questions