how to use like and in query

Question

Possible Duplicate:
Is there a combination of “LIKE” and “IN” in SQL?

can we use like and in condition in same query like this

    select inticap(last_name),length(last_name) from employees
    where last_name in(like '%J%',like '%A%',like '%M%');

OR 

  select inticap(last_name),length(last_name) from employees
where last_name like in( '%J%', '%A%',l'%M%');

i know it wont work . here i want to display initial letter of last name capital and the length of last name and i want to display name with alphabets 'A' ,'J'and 'M'. please help me

duplicate of http://stackoverflow.com/questions/4335359/is-it-possible-to-use-like-and-in-for-a-where-statment and http://stackoverflow.com/questions/10747996/in-sql-server-how-do-i-search-for-multiple-values-in-a-column-where-i-need-to-u/10748091#10748091 and many others — Judge Mental, Jun 08 '12 at 06:07
No it won't work, You can use OR in query like last_name like '%J%' OR like '%A%' — shobhit, Jun 08 '12 at 06:08

score 3 · Answer 1 · answered Jun 08 '12 at 06:09

3

 select inticap(last_name),length(last_name) from employees
where last_name like '%J%' OR last_name like '%A%' OR last_name '%M%';

answered Jun 08 '12 at 06:09

Pramod Kumar

7,914
5
28
37

hi i know it will work but i want to write query with in and like – Girish R Acharya Jun 08 '12 at 06:18
U can look - http://stackoverflow.com/questions/3350106/how-to-use-like-clause-with-in-caluse-in-sql-server – Pramod Kumar Jun 08 '12 at 06:26
1

With respect to the link in the previous comment: instead of creating a table you could also use a piplined function and add your 'like' conditions programmaticly to a PL/SQL table. – Dzik Jun 08 '12 at 06:49

score 3 · Accepted Answer · answered Jun 08 '12 at 13:48

As has been mentioned, you cannot directly combine LIKE and IN as you would prefer.

Here's one kludgy option:

SELECT INITCAP(last_name)
,      LENGTH(last_name)
FROM   employees
WHERE  +1 IN (SIGN(INSTR(last_name,'J'))
          ,   SIGN(INSTR(last_name,'A'))
          ,   SIGN(INSTR(last_name,'M')))

Or, if you're on at least 10g, you can use regular expressions:

SELECT INITCAP(last_name)
,      LENGTH(last_name)
FROM   employees
WHERE  REGEXP_LIKE(last_name,'(J|A|M)')

...but regular expressions in general are more CPU-intensive than your garden-variety SQL functions.

how to use like and in query

2 Answers2