Separate/Organize string values in Oracle

Question

I Have requirement in Oracle 10g: I need to select using an in clause.

The problem is the string which I want to use in the database is comma separated values product codes like 4435D,4436E,5656F, 5670L in a single column.

What I want is something like '4435D','4436E','5656F', '5670L' which I can use as

Select * from sub_products 
where product_code in ('4435D','4436E','5656F', '5670L');

How do we achieve this? Any help would be appreciated.

score 0 · Answer 1 · answered Jul 10 '13 at 07:32

0

How about using like?

Select * from sub_products 
where product_code like '%4435D%' or product_code like '%5656F%' or product_code like '%5670L%';

answered Jul 10 '13 at 07:32

Farid Mohd Nor

145
5

Nope I do know what data would be there I just hard coded for your reference the column could have 1 value or 4 values comma separated . First I have to get them in a proper string format to use it in an IN clause right ? How do we do that ? – user2567381 Jul 10 '13 at 08:11

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

you can use CONTAINS keyword (check this : Is there a combination of "LIKE" and "IN" in SQL?)

WHERE CONTAINS(product_code,'4435D OR 4436E OR 5656F OR 5670L',1)>0

edited May 23 '17 at 10:32

Community

1
1

answered Jul 10 '13 at 07:49

Fabrice Mainguené

486
1
5
18

score 0 · Accepted Answer · answered Jul 10 '13 at 11:54

You can use the hierarchical query to separate the values in the string.

with str as (
  select '4435D,4436E,5656F,5670L' s from dual)
select regexp_substr(s,'[^,]+',1,level) from str
connect by regexp_substr(s,'[^,]+',1,level) is not null;

output:

You can use this as subquery in your query. Sample fiddle

Separate/Organize string values in Oracle

3 Answers3