Comma-separated string in IN clause of SQL query [duplicate]

Question

Possible Duplicate:
how to pass a variable in WHERE IN clause of oracle sql?

I have a column Prefix in my table Tbl_Prefix with value as string separated by comma like this:

'aaa','bbb','ccc'

I have an employee table Tbl_Employee like this:

Empno Prefix
------------
1000  aaa
2000  eee
3000  ccc
4000  aaa
5000  ddd

I need to use this prefix in the IN portion of my WHERE clause in this query:

Select * 
from Tbl_Employee  
where Tbl_Employee.Prefix  in (select  Tbl_Prefix.prefix 
                               from Tbl_Prefix 
                               where Tbl_Prefix.flag = 'y')

The inner select query select Tbl_Prefix.prefix from Tbl_Prefix where Tbl_Prefix.flag='y' has the result 'aaa','bbb','ccc'

How to use this string in the 'IN' clause so that I will get a proper result?

By the way, if those values are stored in you database like that, you should seriously reconsider your database design. Create a child table that contains a record for each of the comma separated values for each parent record. If the data is dumped like this, create a job to process the raw data to a structure like that. With a proper table structure, you will only need a relatively simple inner join. — GolezTrol
– GolezTrol, Commented Nov 4, 2012 at 8:27
Actually this table column is inserted upon user selecting a set of prefix from listbox.List box is populated as aaa,bbb,ccc etc from another table.Whe user select some prefix,I store them as a string in my prefix table column.I need to use this user selected prefix,in the sql query.How can I store this selected prefix in Prefix table ? — Soumya
– Soumya, Commented Nov 4, 2012 at 8:34

Yatrix · Accepted Answer · 2012-11-04 23:34:48Z

1

First, don't do that. Creat a data bucket/link table and link aaa, bbb and ccc to its relevant relationship.

However, if you have to do it this way (change it if possible!), you'll have to create a dynamic SQL string and execute it. So, build the string from the select through the where and append the "Prefix" delimited string, then call EXEC.

So, you'll set:

@sql = 'SELECT YOURSTUFF FROM YOURTABLE WHERE YOURCOLUMN IN ' + @prefix. @prefix should be the column value (aaa,bbb,ccc) from tbl_Prefix you needed.

Once your string is built, EXEC @sql

edited Nov 4, 2012 at 23:34

answered Nov 4, 2012 at 8:27

Yatrix

13.9k17 gold badges57 silver badges83 bronze badges

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

Comments

GolezTrol · Accepted Answer · 2012-11-04 08:30:30Z

0

If those values are stored in you database like that, you should seriously reconsider your database design. Create a child table that contains a record for each of the comma separated values for each parent record.

For now, you can get away using EXISTS and LIKE, but this query will become slower soon if you got a lot of records.

select 
  * 
from 
  Tbl_Employee e
where 
  exists  
    (select * from 
       Tbl_Prefix x
    where
       x.flag = 'y' and
       x.prefix like '%''' + e.prefix + '''%') 
       -- Add quotes to prevent false matches

answered Nov 4, 2012 at 8:30

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Collectives™ on Stack Overflow

Comma-separated string in IN clause of SQL query [duplicate]

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related