Comma separated column values matching [message #647622] |
Thu, 04 February 2016 06:18 |
|
pratik4891
Messages: 73 Registered: February 2011
|
Member |
|
|
Hello ,
I have a column field description which holds comma separated values .
I have to fetch all the rows which matches user given value in any of the comma separated values
id description
1 red,blue,yellow
2 red1,blue1,yellow
3 red2,blue1,yellow1
4 red1,blue1,yellow2
So if user inputs red1 it will return only row 2 and 4
Can anyone please help with the logic ?
create table testing
(
id number,
description varchar(1000)
)
insert into Childrensnational_micro.testing (id,description) values(1,'red,blue,yellow');
insert into Childrensnational_micro.testing (id,description) values(2,'red1,blue1,yellow');
insert into Childrensnational_micro.testing (id,description) values(3,'red2,blue1,yellow1');
insert into Childrensnational_micro.testing (id,description) values(4,'red1,blue1,yellow2');
commit;
|
|
|
|
|
|
|
Re: Comma separated column values matching [message #647627 is a reply to message #647626] |
Thu, 04 February 2016 06:53 |
|
ricky_s
Messages: 21 Registered: February 2016 Location: New Delhi, India
|
Junior Member |
|
|
SELECT * FROM
(
SELECT DISTINCT ID,TRIM(REGEXP_SUBSTR(DESCRIPTION, '[^,]+', 1, LEVEL)) DESCRIPTION
FROM TESTING
CONNECT BY INSTR(DESCRIPTION, ',', 1, LEVEL - 1) > 0
)
WHERE
DESCRIPTION = 'red';
|
|
|
|
|
|
|
|
|
|
|
|
|
Re: Comma separated column values matching [message #647638 is a reply to message #647637] |
Thu, 04 February 2016 08:08 |
|
pratik4891
Messages: 73 Registered: February 2011
|
Member |
|
|
with t as
(select ','||description||',' as desc1 from testing)
select * from t where instr(desc1 , ',red1,') > 0
with t as
(select ','||description||',' as desc1 from testing)
select * from t where instr(desc1 , ',yellow,') > 0
[Updated on: Thu, 04 February 2016 08:09] Report message to a moderator
|
|
|
|
|
|
Re: Comma separated column values matching [message #647661 is a reply to message #647622] |
Thu, 04 February 2016 16:25 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
This is the sort or thing that Oracle Text is designed for. If you create a context index, then you can search quickly using contains, as demonstrated below.
SCOTT@orcl> create table testing
2 (id number,
3 description varchar(1000))
4 /
Table created.
SCOTT@orcl> insert all
2 into testing (id,description) values(1,'red,blue,yellow')
3 into testing (id,description) values(2,'red1,blue1,yellow')
4 into testing (id,description) values(3,'red2,blue1,yellow1')
5 into testing (id,description) values(4,'red1,blue1,yellow2')
6 select * from dual
7 /
4 rows created.
SCOTT@orcl> create index testing_desc_idx on testing (description)
2 indextype is ctxsys.context
3 /
Index created.
SCOTT@orcl> column description format a30
SCOTT@orcl> select * from testing where contains (description, 'red1') > 0
2 /
ID DESCRIPTION
---------- ------------------------------
2 red1,blue1,yellow
4 red1,blue1,yellow2
2 rows selected.
SCOTT@orcl> select * from testing where contains (description, 'yellow') > 0
2 /
ID DESCRIPTION
---------- ------------------------------
1 red,blue,yellow
2 red1,blue1,yellow
2 rows selected.
|
|
|