oracle query to select values using IN operator

Question

I have a string which has list of valid places.

var locations = COLOMBO,DELHI,SINGAPORE

I want to query all the employees belonging to the location mention in the above string

I am using the following query

SELECT emp_id,emp_name 
FROM EMPLOYEE 
WHERE emp_location IN (locations) ;

Oracle is considering it as single string and searching for the below query string

SELECT emp_id,emp_name 
FROM EMPLOYEE 
WHERE emp_location IN ('COLOMBO,DELHI,SINGAPORE');

I want to know if there is any way I can pass the string and get the valid emp_id,emp_name belonging to the location mentioned in the string.

Ram · Accepted Answer · 2015-07-14 15:51:42Z

3

As mentioned here

First you need to use the REGEXP_SUBSTR function to split the string into individual strings

VAR locations = 'COLOMBO,DELHI,SINGAPORE';

SELECT REGEXP_SUBSTR(locations,'[^,]+', 1, level) 
FROM dual
CONNECT BY REGEXP_SUBSTR(locations, '[^,]+', 1, level) IS NOT NULL;

Then use it as a sub query in a select to get the desired result

VAR locations = 'COLOMBO,DELHI,SINGAPORE';

SELECT emp_id,emp_name FROM EMPLOYEE 
WHERE emp_location in(
SELECT REGEXP_SUBSTR(locations,'[^,]+', 1, level) FROM dual
CONNECT BY REGEXP_SUBSTR(locations, '[^,]+', 1, level) IS NOT NULL);

edited Jul 14, 2015 at 15:51

answered Oct 17, 2014 at 4:19

Ram

3,09110 gold badges43 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Thorsten Kettner · Accepted Answer · 2014-10-17 08:56:28Z

1

You are looking for locations mentioned in the string, so actually for locations that are a substring of your string. Oracle offers INSTR for such a search.

Only be aware to look for complete cities from comma to comma (add commas in front and end). So not to find 'YORK' in 'LONDON,NEW YORK' (for ',YORK,' is not a substring of ',LONDON,NEW YORK,' wheras 'YORK' without commas would be a substring of 'LONDON,NEW YORK').

select emp_id,emp_name 
from EMPLOYEE 
where instr(','|| locations || ',' , ',' || emp_location || ',') > 0;

answered Oct 17, 2014 at 8:56

Thorsten Kettner

96.6k8 gold badges56 silver badges81 bronze badges

Collectives™ on Stack Overflow

oracle query to select values using IN operator

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related