Home » SQL & PL/SQL » SQL & PL/SQL » Regexp magic ! (I hope) (11.2.0.3.0)
Regexp magic ! (I hope) [message #662041] Fri, 14 April 2017 18:20 Go to next message
Amine
Messages: 304
Registered: March 2010
Senior Member

hi all,
it's been a while ! I missed you !
I need to check that a varchar satisfies one of the patterns below :

n1/A
n1+...+nx/A
n1+...+nx/A+n3+...+ny/B

I think regexp could do it, but how ?

Thanks in advance

Amine
Re: Regexp magic ! (I hope) [message #662042 is a reply to message #662041] Sat, 15 April 2017 01:06 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Can you post a test case.
Is "+" really "+" or not?
Is "nx" really "nx" or not?
Is "n" really "n" or not?
Is "A" really "A" or not?
Is "B" really" "B" or not?

Re: Regexp magic ! (I hope) [message #662089 is a reply to message #662042] Mon, 17 April 2017 15:58 Go to previous messageGo to next message
Amine
Messages: 304
Registered: March 2010
Senior Member

Here is what I need to do.
Users have to enter a string and I have to process it.
Users enter identifiers (numbers), separated by the + sign, for a particular object.
Example :
454+787+965+6889+555+5555/B1
Here a string that has 6 identifiers for the object B1.

Also, users can enter multiple strings such the below separated by the + sign.
Example :
454+787+965+6889+555+5555/B1+86+69+778+51+11+256/B2

Here, we have identifiers for object B1 and for object B2.


So to answer Michel :

Is "+" really "+" or not? yes it is

Is "nx" really "nx" or not? nx are integers
Is "n" really "n" or not?...
Is "A" really "A" or not? A is a really A
Is "B" really" "B" or not? B is really B

Thanks in advance,

Amine
Re: Regexp magic ! (I hope) [message #662093 is a reply to message #662089] Mon, 17 April 2017 22:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8790
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select * from test_data
  2  /

TEST_COL
---------------------------------------------------
454+787+965+6889+555+5555/B1+86+69+778+51+11+256/B2
454+787+965+6889+555+5555/B1

2 rows selected.

SCOTT@orcl_12.1.0.2.0> column obj format a15
SCOTT@orcl_12.1.0.2.0> column id  format a15
SCOTT@orcl_12.1.0.2.0> select obj,
  2  	    regexp_substr(ids,'[^+]+',1,column_value) id
  3  from   (select substr(obj_and_ids,instr(obj_and_ids,'/')+1) obj,
  4  		    substr(obj_and_ids,1,instr(obj_and_ids,'/')-1) ids
  5  	     from   (select ltrim(regexp_substr(test_col,'[^/]+[^+]+',1,column_value),'+') obj_and_ids
  6  		     from   test_data,
  7  			    table
  8  			      (cast
  9  				 (multiset
 10  				    (select rownum
 11  				     from   dual
 12  				     connect by level <= regexp_count(test_col,'/'))
 13  				  as sys.odcinumberlist)))),
 14  	    table
 15  	      (cast
 16  		 (multiset
 17  		    (select rownum
 18  		     from   dual
 19  		     connect by level <= regexp_count(ids,'\+')+1)
 20  		  as sys.odcinumberlist))
 21  /

OBJ             ID
--------------- ---------------
B1              454
B1              787
B1              965
B1              6889
B1              555
B1              5555
B2              86
B2              69
B2              778
B2              51
B2              11
B2              256
B1              454
B1              787
B1              965
B1              6889
B1              555
B1              5555

18 rows selected.


Re: Regexp magic ! (I hope) [message #662096 is a reply to message #662089] Tue, 18 April 2017 00:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Is "A" really "A" or not? A is a really A
So why your example shows B1?

Quote:
Is "B" really" "B" or not? B is really B
So why your example shows B2?

Quote:
454+787+965+6889+555+5555/B1+86+69+778+51+11+256/B2
How do we know that "/B1+86" is "/B1" then "86" and not "/B" then "1+86"?
Especially given that your "specifications" do not match your "example".

Re: Regexp magic ! (I hope) [message #662150 is a reply to message #662096] Wed, 19 April 2017 01:31 Go to previous messageGo to next message
Amine
Messages: 304
Registered: March 2010
Senior Member

Thanks miss Oracle. That's exactly what I wanted to do. Thanks to Michel too.
Re: Regexp magic ! (I hope) [message #662153 is a reply to message #662150] Wed, 19 April 2017 01:47 Go to previous message
Michel Cadot
Messages: 65317
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

But you didn't answer to my questions...

Previous Topic: pl/sql procedure using cursors
Next Topic: merge issue
Goto Forum:
  


Current Time: Thu Jan 18 09:01:38 CST 2018

Total time taken to generate the page: 0.01123 seconds