Home » SQL & PL/SQL » SQL & PL/SQL » Comma separated column values matching (10.0.0.1)
Comma separated column values matching [message #647622] Thu, 04 February 2016 06:18 Go to next message
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 #647623 is a reply to message #647622] Thu, 04 February 2016 06:23 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Use the following:

SELECT * FROM TESTING WHERE DESCRIPTION LIKE '%'||:PARAM||'%';
Re: Comma separated column values matching [message #647624 is a reply to message #647623] Thu, 04 February 2016 06:31 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
Its not working.
can you please sent the query with the keyword "red1"

I have changed :PARAM with red1 but showing column "red1" doesnt exist.
Re: Comma separated column values matching [message #647625 is a reply to message #647624] Thu, 04 February 2016 06:39 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
SELECT * FROM TESTING WHERE DESCRIPTION LIKE '%red1%';



:param is a bind variable whose value will be given at run time.
Re: Comma separated column values matching [message #647626 is a reply to message #647625] Thu, 04 February 2016 06:43 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
Sorry I dont think I explained my issue clear enough.

What you have provided regular expression ,so if another row with red12 or ared1 will come then those will be selected as well.

We need to pick up the exact value from comma separated values.
So if I provide red then it will pick up only row1
Re: Comma separated column values matching [message #647627 is a reply to message #647626] Thu, 04 February 2016 06:53 Go to previous messageGo to next message
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 #647628 is a reply to message #647627] Thu, 04 February 2016 06:56 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
Thanks a lot .
I have to implement without level so if you please send me the logic I will try to find another way.
Re: Comma separated column values matching [message #647629 is a reply to message #647628] Thu, 04 February 2016 06:57 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Why does it need to be without Level.
Re: Comma separated column values matching [message #647630 is a reply to message #647629] Thu, 04 February 2016 07:11 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
I have to put the query in customized platform where level wont work.
Re: Comma separated column values matching [message #647631 is a reply to message #647630] Thu, 04 February 2016 07:23 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Since, i don't know what will work, i can suggest :

Creating a function with the above query. Pass the search value as input parameter and return the result.
Then call that function in your query.

Hope that helps
Re: Comma separated column values matching [message #647632 is a reply to message #647631] Thu, 04 February 2016 07:34 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
Thanks a lot.
It worked by concatenate the user value with comma and using instr

Thanks for your continuous support
Re: Comma separated column values matching [message #647633 is a reply to message #647631] Thu, 04 February 2016 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Since, i don't know what will work, i can suggest :


Read row generator for various way to create row generator with or without LEVEL.

Re: Comma separated column values matching [message #647634 is a reply to message #647633] Thu, 04 February 2016 07:54 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

Quote:
It worked by concatenate the user value with comma and using instr

That's NOT the solution: how will you find 'yellow' then?
Re: Comma separated column values matching [message #647635 is a reply to message #647634] Thu, 04 February 2016 07:56 Go to previous messageGo to next message
pratik4891
Messages: 73
Registered: February 2011
Member
In the description also I am adding comma before and after then matching
Re: Comma separated column values matching [message #647636 is a reply to message #647635] Thu, 04 February 2016 07:59 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So post the actual SQL statement instead of trying (and failing) to describe it.

Re: Comma separated column values matching [message #647637 is a reply to message #647636] Thu, 04 February 2016 08:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Also post the solution you found in your previous topics and you still did not post.

Re: Comma separated column values matching [message #647638 is a reply to message #647637] Thu, 04 February 2016 08:08 Go to previous messageGo to next message
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 #647639 is a reply to message #647638] Thu, 04 February 2016 08:21 Go to previous messageGo to next message
JNagtzaam
Messages: 36
Registered: July 2015
Location: Alkmaar
Member

That will work. If you are interested in other solutions, XMLTABLE for example: https://lalitkumarb.com/2015/03/04/split-comma-delimited-strings-in-a-table-in-oracle
Re: Comma separated column values matching [message #647640 is a reply to message #647633] Thu, 04 February 2016 08:24 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
Michel Cadot wrote on Thu, 04 February 2016 19:22

Quote:
Since, i don't know what will work, i can suggest :


Should have paraphrased it as "Since, I don't know what will work from the customized platform that is being used"

Read row generator for various way to create row generator with or without LEVEL.



Thanks for pointing me to row generator.
Re: Comma separated column values matching [message #647641 is a reply to message #647634] Thu, 04 February 2016 08:35 Go to previous messageGo to next message
ricky_s
Messages: 21
Registered: February 2016
Location: New Delhi, India
Junior Member
JNagtzaam wrote on Thu, 04 February 2016 19:24
Quote:
It worked by concatenate the user value with comma and using instr

That's NOT the solution: how will you find 'yellow' then?


If you are referring to multiple values being returned, we could simply use a pipelined function to return multiple
rows.
Re: Comma separated column values matching [message #647661 is a reply to message #647622] Thu, 04 February 2016 16:25 Go to previous message
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.


Previous Topic: How to use DISTINCT for CLOB data type?
Next Topic: change permanently the value of the initialization parameter
Goto Forum:
  


Current Time: Thu Mar 28 11:19:54 CDT 2024