IN with LIKE Condition in SQL

Question

Is it possible to use LIKE inside an IN? The following query produces an error.

    Select * 
    from employees
    where salary between 2500 and 10000
    and job_id NOT in (like '%MAN%', like '%CLERK%');

What is the correct syntax? I'm using Oracle 11g

This *in like* thingy is a feature I wanted so many times. Oracle doesn't have such a thing. — René Nyffenegger, Feb 15 '14 at 08:57
@RenéNyffenegger: I don't think any database has something like that. — a_horse_with_no_name, Feb 15 '14 at 09:05
@a_horse_with_no_name: Teradata has support for **LIKE ALL|ANY ('%MAN%','%CLERK%')**. ALL -> ANDed LIKEs, ANY -> ORed LIKEs, you might even use a SELECT to get the search strings — dnoeth, Feb 15 '14 at 12:43
@dnoeth PostgreSQL has a similar feature with arrays: `LIKE ANY ARRAY ('%MAN%', %CLERK%')` — Mureinik, Feb 15 '14 at 17:05
You look for `like any (...)` construct which does not work in Oracle but you can mimic it with `exists` and concatenated like expression, see my answer under linked question. (You can look also at the Sokrates` solution at AskTom.) — Tomáš Záluský, Jun 23 '17 at 16:32

score 4 · Answer 1 · edited Feb 15 '14 at 09:00

4

You can't do both 'like' and 'in' at the time, but you can do this

    Select * 
      from employees
     where salary between 2500 
       and 10000
       and job_id NOT like '%MAN%' 
       and job_id NOT like '%CLERK%';

edited Feb 15 '14 at 09:00

GitaarLAB

14,134
11
55
76

answered Feb 15 '14 at 08:40

Elfentech

747
5
10

This is what I did after I got an error for the query I mentioned. Thanks – Ashiq Irphan Feb 15 '14 at 16:58

score 3 · Answer 2 · edited Feb 18 '14 at 10:32

Unfortunately, there is no "in like" operator. You could break it up to a series of LIKE conditions like @Elfentech suggested, or you could emulate it with the REGEXP_LIKE function:

SELECT *
FROM   employees
WHERE  salary BETWEEN 2500 AND 10000
AND    NOT REGEXP_LIKE (job_id, '.*[MAN|CLERK].*')

Alternatively, you could join on a bunch of UNION ALL queries from dual:

SELECT *
FROM  employees
JOIN  (SELECT 'MAN' AS search FROM dual 
       UNION ALL
       SELECT 'CLERK' FROM dual) jobs 
ON    employee.jod_id LIKE CONCAT ('%', jobs.search, '%')
WHERE salary BETWEEN 2500 AND 10000

Thanks for the `REGEXP_LIKE` – Ashiq Irphan Feb 15 '14 at 17:00 — Ashiq Irphan, Feb 15 '14 at 17:00

IN with LIKE Condition in SQL

2 Answers2