OraFAQ Forum: SQL & PL/SQL » Regexp magic ! (I hope)

Home » SQL & PL/SQL » SQL & PL/SQL » Regexp magic ! (I hope) (11.2.0.3.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Regexp magic ! (I hope) [message #662041]

Fri, 14 April 2017 18:20

Amine
Messages: 375
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

Report message to a moderator

Re: Regexp magic ! (I hope) [message #662042 is a reply to message #662041]

Sat, 15 April 2017 01:06

Michel Cadot
Messages: 68645
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?

Report message to a moderator

Re: Regexp magic ! (I hope) [message #662089 is a reply to message #662042]

Mon, 17 April 2017 15:58

Amine
Messages: 375
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

Report message to a moderator

Re: Regexp magic ! (I hope) [message #662093 is a reply to message #662089]

Mon, 17 April 2017 22:38

Barbara Boehmer
Messages: 9090
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.

Report message to a moderator