Home » SQL & PL/SQL » SQL & PL/SQL » Search through REGEXP_INSTR function (Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod)
Search through REGEXP_INSTR function [message #331391] Thu, 03 July 2008 04:06 Go to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

I want get the rows which are having any values of the input values. For example when '12,18,,33,23' is passed as input,
then list all the rows which have 12, 18, 33, 23 anywhere in the list? I can do it in PL/SQL but is there anyway to directly do it in SQL.
Any way it can be achieved through REGEXP_INSTR?

create table test (col1 varchar2(100));

insert into test values ('12,13,14,15');
insert into test values ('14,18,19,20');
insert into test values ('21,22,23,25');
insert into test values ('12,22,28,31');

DECLARE
 lv_val VARCHAR2(100) := ',12,18,,33,23,';
 pi_val VARCHAR(10);
 i NUMBER := 1;
 start_pos NUMBER := 0;
 end_pos NUMBER := 0;
 XX VARCHAR(100);
BEGIN
 LOOP
  start_pos := INSTR(lv_val,',',1,i);
  end_pos   := INSTR(lv_val,',',1,i+1);
  pi_val := SUBSTR(lv_val,start_pos+1,end_pos - start_pos -1);
  SELECT max(col1) INTO XX FROM test WHERE INSTR(col1,pi_val) > 0;
  dbms_output.put_line('S ' || XX);
  i := i + 1;
 EXIT WHEN start_pos = end_pos;
 END LOOP;
END;
/

S 12,22,28,31
S 14,18,19,20
S
S
S 21,22,23,25
S
S

PL/SQL procedure successfully completed.
Re: Search through REGEXP_INSTR function [message #331403 is a reply to message #331391] Thu, 03 July 2008 04:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
then list all the rows which have 12, 18, 33, 23 anywhere in the list?

Which have all these numbers in this order?
Which have all these numbers in any order?
Which have any of these numbers?

Regards
Michel
Re: Search through REGEXP_INSTR function [message #331422 is a reply to message #331403] Thu, 03 July 2008 05:07 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Michel Cadot wrote on Thu, 03 July 2008 15:05
Quote:
then list all the rows which have 12, 18, 33, 23 anywhere in the list?

Which have all these numbers in this order?
Which have all these numbers in any order?
Which have any of these numbers?

Regards
Michel



Which have any of these numbers? - I am looking for this as you can see from the example below..
Re: Search through REGEXP_INSTR function [message #331427 is a reply to message #331403] Thu, 03 July 2008 05:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
All three, and not a REGEXP in sight:
create table test_0036 (col1 varchar2(100));

insert into test_0036 values ('12,13,14,15');
insert into test_0036 values ('14,18,19,20');
insert into test_0036 values ('21,22,23,25');
insert into test_0036 values ('12,22,28,31');
 
select trim(',' FROM col1) col1
from  (select ','||col1||',' col1 from test_0036)
where  instr(col1,',12,') >0
and    instr(col1,',18,') >0
and    instr(col1,',33,') >0
and    instr(col1,',23,') >0;

select trim(',' FROM col1) col1 
from  (select ','||col1||',' col1 from test_0036)
where  instr(col1,',12,') >0
or     instr(col1,',18,') >0
or     instr(col1,',33,') >0
or     instr(col1,',23,') >0;

select trim(',' FROM col1) col1
from  (select ','||col1||',' col1 from test_0036)
where  instr(col1,',12,') >0
and    instr(col1,',18,') > instr(col1,',12,')
and    instr(col1,',33,') > instr(col1,',18,')
and    instr(col1,',23,') > instr(col1,',33,');
Re: Search through REGEXP_INSTR function [message #331431 is a reply to message #331422] Thu, 03 July 2008 05:29 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Which have any of these numbers? - I am looking for this as you can see from the example below..

What example?

Regards
Michel
Re: Search through REGEXP_INSTR function [message #331434 is a reply to message #331427] Thu, 03 July 2008 05:35 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

JRowbottom wrote on Thu, 03 July 2008 15:50
All three, and not a REGEXP in sight:
create table test_0036 (col1 varchar2(100));

insert into test_0036 values ('12,13,14,15');
insert into test_0036 values ('14,18,19,20');
insert into test_0036 values ('21,22,23,25');
insert into test_0036 values ('12,22,28,31');
 
select trim(',' FROM col1) col1
from  (select ','||col1||',' col1 from test_0036)
where  instr(col1,',12,') >0
and    instr(col1,',18,') >0
and    instr(col1,',33,') >0
and    instr(col1,',23,') >0;

select trim(',' FROM col1) col1 
from  (select ','||col1||',' col1 from test_0036)
where  instr(col1,',12,') >0
or     instr(col1,',18,') >0
or     instr(col1,',33,') >0
or     instr(col1,',23,') >0;

select trim(',' FROM col1) col1
from  (select ','||col1||',' col1 from test_0036)
where  instr(col1,',12,') >0
and    instr(col1,',18,') > instr(col1,',12,')
and    instr(col1,',33,') > instr(col1,',18,')
and    instr(col1,',23,') > instr(col1,',33,');




I think you got the requirement wrong. It can be any number of values which is passed.

',12,18,,33,23,' can be
',12,18,,33,23,35,56' anything.. so can't hardcord the values...
Re: Search through REGEXP_INSTR function [message #331446 is a reply to message #331434] Thu, 03 July 2008 06:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at the following to convert a string list to a real list:
http://www.orafaq.com/forum/m/328430/102589/?#msg_328430

Regards
Michel
Re: Search through REGEXP_INSTR function [message #331450 is a reply to message #331434] Thu, 03 July 2008 06:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
select col1,single_term,case when instr(',12,18,,33,23,',single_term) >0 then 'Match' else 'No Match' end
from (
select col1,lvl,substr(col1,instr(col1,',',1,lvl),instr(col1,',',1,lvl+1)-instr(col1,',',1,lvl)+1) single_term 
from  (select ','||col1||',' col1 from test_0036)
     ,(select level lvl from dual connect by level <= 1000)
where lvl <= length(col1)-length(replace(col1,',',''))-1);
Re: Search through REGEXP_INSTR function [message #331452 is a reply to message #331434] Thu, 03 July 2008 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> print :lv_val
LV_VAL
-------------------
,12,18,,33,23,

SQL> with 
  2    in_values as (
  3      select substr(:lv_val,
  4                    instr(:lv_val, ',', 1, rn)+1,
  5                    instr(:lv_val, ',', 1, rn+1) - instr(:lv_val, ',', 1, rn) - 1) 
  6               value
  7      from (select level rn from dual 
  8            connect by level < length(:lv_val)-length(replace(:lv_val,',','')))
  9      where instr(:lv_val, ',', 1, rn+1)-instr(:lv_val, ',', 1, rn) > 1
 10    )
 11  select col1 
 12  from test, in_values
 13  group by col1
 14  having sum(instr(col1,value)) > 0
 15  /
COL1
-------------------------------
21,22,23,25
12,13,14,15
14,18,19,20
12,22,28,31

4 rows selected.

Regards
Michel
Re: Search through REGEXP_INSTR function [message #331524 is a reply to message #331391] Thu, 03 July 2008 14:52 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
SQL> select * from test;

COL1
--------------------
12,13,14,15
14,18,19,20
21,22,23,25
12,22,28,31
1,2,3,4

SQL> var v_val varchar2(20)
SQL> exec :v_val := ',12,18,,33,23,';

PL/SQL procedure successfully completed.

SQL> select col1 from test
  2  where regexp_like(col1, '('||regexp_replace(trim(',' from :v_val), ',{1,}','|')||')');

COL1
--------------------
12,13,14,15
14,18,19,20
21,22,23,25
12,22,28,31

SQL> exec :v_val := ',12,18,,33,23,35,56';

PL/SQL procedure successfully completed.

SQL> select col1 from test
  2  where regexp_like(col1, '('||regexp_replace(trim(',' from :v_val), ',{1,}','|')||')');

COL1
--------------------
12,13,14,15
14,18,19,20
21,22,23,25
12,22,28,31

SQL>
Re: Search through REGEXP_INSTR function [message #331558 is a reply to message #331391] Fri, 04 July 2008 00:23 Go to previous messageGo to next message
himang
Messages: 282
Registered: March 2005
Location: Bangalore
Senior Member

Excellent Ebrian. This is just great and what I was looking for. I knew it can be acheieved through REGEXP functions, but was not sure how..

Thanks again for your help.
Re: Search through REGEXP_INSTR function [message #331585 is a reply to message #331524] Fri, 04 July 2008 02:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very elegant solution, but I think it still needs a little tweaking: You can get false positives if there are 3 digit numbers in the comma separated list of values in the table, or 1 digit numbers in the contents of :val
SQL> insert into test_0036 values ('120,34,87,45');

1 row created.

SQL> 
SQL> select col1,regexp_replace(trim(',' from ',12,18,,33,23,35,56'), ',{1,}','|') from test_0036
  2  where regexp_like(col1, '('||regexp_replace(trim(',' from ',12,18,,33,23,35,56'), ',{1,}','|')||')');

COL1                REGEXP_REPLACE(TR
------------------- -----------------
12,13,14,15         12|18|33|23|35|56
14,18,19,20         12|18|33|23|35|56
21,22,23,25         12|18|33|23|35|56
12,22,28,31         12|18|33|23|35|56
120,34,87,45        12|18|33|23|35|56

SQL> select col1,regexp_replace(trim(',' from ',1,,33,23,35,56'), ',{1,}','|') from test_0036
  2  where regexp_like(col1, '('||regexp_replace(trim(',' from ',12,18,,33,23,35,56'), ',{1,}','|')||')');

COL1                REGEXP_REPLAC
------------------- -------------
12,13,14,15         1|33|23|35|56
14,18,19,20         1|33|23|35|56
21,22,23,25         1|33|23|35|56
12,22,28,31         1|33|23|35|56
120,34,87,45        1|33|23|35|56


Re: Search through REGEXP_INSTR function [message #331659 is a reply to message #331585] Fri, 04 July 2008 07:20 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Thanks for that catch JRowbottom. This should accommodate that situation.

SQL> select * from test;

COL1
--------------------
12,13,14,15
14,18,19,20
21,22,23,25
12,22,28,31
120,34,87,45
33
1

7 rows selected.

SQL> var v_val varchar2(20)
SQL> exec :v_val := ',1,18,,33,23,35,56';

PL/SQL procedure successfully completed.

SQL> select col1 from test
  2  where regexp_like(','||col1||',', '(,'||regexp_replace(trim(',' from :v_val), ',{1,}',',|,')||',)');

COL1
--------------------
14,18,19,20
21,22,23,25
33
1
Previous Topic: Join
Next Topic: Flip Flop Pragma
Goto Forum:
  


Current Time: Tue Dec 06 12:37:01 CST 2016

Total time taken to generate the page: 0.12289 seconds