OraFAQ Forum: SQL & PL/SQL » Reqular expression

Home » SQL & PL/SQL » SQL & PL/SQL » Reqular expression (Oracle 11.2.0.3)

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

Reqular expression [message #616384]

Mon, 16 June 2014 08:15

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I was trying some sort of regular expression stuff and come across the below.

select  REGEXP_SUBSTR('22.2222', '([.][[:digit:]]{1,2})?') hello from dual

The above code returns null

I was intend to retrieve substring from the string where substring should match "dot followed by one or two digits and this set may occure once or zero times" -- so I have included ()?
I am sure we can achive this by other ways but I am trying to understand what is wrong with the query why '(' and ')' makes difference.

where as the below works

select  REGEXP_SUBSTR('22.2222', '[.][[:digit:]]{1,2}?') hello from dual

returns .2

Regards,
Pointers

Report message to a moderator

Re: Reqular expression [message #616385 is a reply to message #616384]

Mon, 16 June 2014 08:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

and this set may occure once or zero times"

If it happens 0 times then what should be the result?
I don't understand what you are trying to get.
Post several examples with and without the pattern you're searching.

Report message to a moderator

Re: Reqular expression [message #616388 is a reply to message #616385]

Mon, 16 June 2014 08:38

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi Micheal,

As usual thank you for your time.

Actually, I dont have any specific task to achive, I was just trying to understand to see what is wrong with the expression to get
"dot followed by one or two digits and this set may occure once or zero times if it mathches it should return dot followed by one or two digits if it does not then null"

which is what I expected from the below query, to be precise, i am going in reverse engineering.

select  REGEXP_SUBSTR('22.2222', '([.][[:digit:]]{1,2})?') hello from dual

But I think your below question makes me to think in the right direction.
Quote:

If it happens 0 times then what should be the result?

But the below retuns some value though I have not mentioned what to return if the condition is not matched

select  REGEXP_SUBSTR('12.3456', '[.]?[[:digit:]]{1,2}?') hello from dual though I

So I think it is something to do with '()' I may be thinking completly wrong but pls bare with me help me to understand.

Regards,
Pointers

p.s I have modified the first code

[Updated on: Mon, 16 June 2014 08:40]

Report message to a moderator

Re: Reqular expression [message #616418 is a reply to message #616388]

Mon, 16 June 2014 13:52

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

pointers wrote on Mon, 16 June 2014 09:38

I was just trying to understand to see what is wrong with the expression

Then meditate over:

SQL> select  REGEXP_SUBSTR('22.2222', '([.][[:digit:]]{1,2})?') hello from dual
  2  /

H
-


SQL> select  REGEXP_SUBSTR('22.2222', '([.][[:digit:]]{1,2})?',1,2) hello from dual
  2  /

H
-


SQL> select  REGEXP_SUBSTR('22.2222', '([.][[:digit:]]{1,2})?',1,3) hello from dual
  2  /

HEL
---
.22

SQL>

Hope it will give you a clue to your question.

SY.

Report message to a moderator

Re: Reqular expression [message #616441 is a reply to message #616418]

Tue, 17 June 2014 00:51

pointers
Messages: 451
Registered: May 2008

Senior Member

Thank you very much Solomon, I've got what you are trying to say.

Regards,
Pointers

Report message to a moderator

Re: Reqular expression [message #616491 is a reply to message #616441]

Tue, 17 June 2014 08:43

pointers
Messages: 451
Registered: May 2008

Senior Member

Hi,

I am doing reverse engineering, I am only analyzing how the regexp_substr works internally (kind of its algorithm)--not any specific task.

Could you help me to understand why

select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]*',1,2) hello from dual

does not return anything

where as

select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]*',1,1) hello from dual

returns 'www'

I assume regexp_substr(---,1,1) search for anycharacter from the first position and for the first occurance other than '.' hence it returns 'www' so, I was guessing regexp_substr(----,1,2) returns 'abcd', is my undestanding incorrect.

Please bare with me if you find it silly and help me to understand.

Regards,
Pointers

Report message to a moderator

Re: Reqular expression [message #616492 is a reply to message #616491]

Tue, 17 June 2014 09:25

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

You keep stepping on same rakes. Again, meditate over:

SQL> select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]*',1,1) hello from dual;

HEL
---
www

SQL> select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]*',1,2) hello from dual;

H
-


SQL> select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]*',1,3) hello from dual;

HELL
----
abcd

SQL>

SY.

Report message to a moderator

Re: Reqular expression [message #616493 is a reply to message #616492]

Tue, 17 June 2014 09:30

pointers
Messages: 451
Registered: May 2008

Senior Member

@SY: I think I am missing some point here, if you dont mind, could you please explain how

select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]*',1,2) hello from dual

is interpreted by oracle. why it would return null for second occurance --may be theory behind this in simpler terms.

Thank you in advance.

Regards,
Pointers

Report message to a moderator

Re: Reqular expression [message #616497 is a reply to message #616493]

Tue, 17 June 2014 10:04

pointers
Messages: 451
Registered: May 2008

Senior Member

I have found the reason my self for the above issue. Below is what I found for the continuation of reading.

I have analyzed and understood it using REGEXP_REPLACE to find what is first and second occurance in REGEXP_SUBSTR for '*' and REGEXP_SUBSTR for '+'
like

select regexp_replace('www.abcd.net.abcd.nnnn.llll','[^.]*','z',1,1) hello from dual;
output:z.abcd.net.abcd.nnnn.llll

select regexp_replace('www.abcd.net.abcd.nnnn.llll','[^.]*','z',1,2) hello from dual;
output:wwwz.abcd.net.abcd.nnnn.llll

I could find that, because '*' matches to no character (zero occurance) also, regexp_substr(---,1,2) is matching to 'nocharacter' after 'www' and hence it returns null (i just say nocharacter).
So, '*' is making things bit hard to understand.
So I have used '+' instead of '*'
like

select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]+',1,1) hello from dual 
select regexp_substr('www.abcd.net.abcd.nnnn.llll','[^.]+',1,2) hello from dual

after which I am clear now what went wrong.

Regards,
Pointers

Report message to a moderator

Re: Reqular expression [message #616499 is a reply to message #616493]

Tue, 17 June 2014 10:44

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

pointers wrote on Tue, 17 June 2014 10:30

why it would return null for second occurance

Because * means ZERO or more occurrences. It is much simpler to understand what's going on if you compare:

SQL> select regexp_replace('www.abcd.net.abcd.nnnn.llll','([^.]*)','[\1]') from dual
  2  /

REGEXP_REPLACE('WWW.ABCD.NET.ABCD.NNNN.LLLL','([^.]
---------------------------------------------------
[www][].[abcd][].[net][].[abcd][].[nnnn][].[llll][]

SQL> select regexp_replace('www.abcd.net.abcd.nnnn.llll','([^.]+)','[\1]') from dual
  2  /

REGEXP_REPLACE('WWW.ABCD.NET.ABCD.NNNN.
---------------------------------------
[www].[abcd].[net].[abcd].[nnnn].[llll]

SQL>

SY.

Report message to a moderator

Previous Topic:	ORA-06502: PL/SQL: numeric or value error in xmltype.getClobVal
Next Topic:	Looping or SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 12:20:21 CDT 2024