OraFAQ Forum: SQL & PL/SQL » Regexp_SUBSTR on an array returning no value

Home » SQL & PL/SQL » SQL & PL/SQL » Regexp_SUBSTR on an array returning no value (Oracle version 11.2 using windows 7 and Toad 11.5.0)

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

Regexp_SUBSTR on an array returning no value [message #597175]

Tue, 01 October 2013 08:02

caippers
Messages: 6
Registered: October 2013
Location: Des Moines

Junior Member

I'm trying to query an array where we will have mathnames that have the follow format:
variablemathname[00000]

I'm new to regular expresions and have been digging around for a few days to try and simplify my existing query. As of right now i'm hacking it together to bring back the bracketed array value by using INSTR and SUBSTR. This works and gets me the correct results but I want to clean the code up by using regexp_SUBSTR.

In my reading up on regular expression I've tried to create my pattern by using [.] which I believe to be [any character]. I want it to start at the beginning of the string so I've used [^ and I only want the one occurrence so I've ended my expression with ]

I tried using the escape \ before my pattern as I know that [ is a metacharacter but I receive the same results.

Here is my query right now that i'm trying to use to get the expression correct.

SELECT REGEXP_SUBSTR('variablemathname[00000]', '[.],[^,],') RESULT
FROM DUAL;

My expectation is it will bring back the following [00000] but the way it is written now is bringing back nothing.

I would appreciate any insight.

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597177 is a reply to message #597175]

Tue, 01 October 2013 08:12

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

Senior Member
Account Moderator

SQL> select regexp_substr('variablemathname[00000]','\[[^\]*\]') from dual;
REGEXP_
-------
[00000]

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597186 is a reply to message #597177]

Tue, 01 October 2013 08:22

caippers
Messages: 6
Registered: October 2013
Location: Des Moines

Junior Member

Michel Cadot wrote on Tue, 01 October 2013 08:12

SQL> select regexp_substr('variablemathname[00000]','\[[^\]*\]') from dual;
REGEXP_
-------
[00000]

I hope it's not asking to much but can you give me a short explanation of where i went wrong?

I really appreciate your help on this!

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597194 is a reply to message #597186]

Tue, 01 October 2013 09:13

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

Senior Member
Account Moderator

I didn't see you posted your query. Smile

"[.]": any character, it is the same thing than just a single .
"," a comma
"[^,]" not a comma
so
"[.],[^,]," any character followed by a comma followed by any character but a comma followed by a comma.
For instance, "M,C,"
As this pattern does not exist in your string, it returns NULL.

For my pattern "\[[^\]*\]":
"\[" an open square bracket (protected by \ as it is a key character for pattern matching)
"[^\]]" any character that is not a close square bracket \] (protected for the same reason)
"[^\]*" any string (maybe empty one) that does not contain ]
"\]" a close square bracket
so "\[[^\]*\]" means an open square bracket followed by any number of characters up to the first close square bracket.

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597195 is a reply to message #597194]

Tue, 01 October 2013 09:41

caippers
Messages: 6
Registered: October 2013
Location: Des Moines

Junior Member

Thank you so much!!! I appreciate the explanation.

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597200 is a reply to message #597195]

Tue, 01 October 2013 10:01

caippers
Messages: 6
Registered: October 2013
Location: Des Moines

Junior Member

One last question.... I was using the following syntax from PSOUG for REGEXP_SUBSTR:

REGEXP_SUBSTR(source_string, pattern
[, position [, occurrence
[, match_parameter]]])

I see that you do not have "," to separate each portion of the REGEXP. So are commas not always needed to break out each section of the regexp?

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597204 is a reply to message #597200]

Tue, 01 October 2013 10:13

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Michel hasn't bothered using the last 3 parameters and they are optional, so he doesn't need extra commas

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597207 is a reply to message #597204]

Tue, 01 October 2013 10:20

caippers
Messages: 6
Registered: October 2013
Location: Des Moines

Junior Member

Perfect. I appreciate all of your help. It's much appreciated.

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597209 is a reply to message #597207]

Tue, 01 October 2013 10:34

DrabJay
Messages: 32
Registered: May 2013

Member

However, to achieve what I believe is Michel's intention of being non-greedy you should not escape all the brackets

select regexp_substr('variablemathname[00000]','\[[^\]*\]') from dual;
[0000]

select regexp_substr('variablemathname[00000]','\[[^]]*\]') from dual;
[0000]

select regexp_substr('variablemathname[00000]another[1111]','\[[^\]*\]') from dual;
[00000]another[1111]

select regexp_substr('variablemathname[00000]another[1111]','\[[^]]*\]') from dual;
[00000]

Although with your format this would not matter.

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597211 is a reply to message #597209]

Tue, 01 October 2013 10:50

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

Senior Member
Account Moderator

Thanks for the fix.
Watching with a closer look, I see that my expression works by chance.
You have indeed not to and even must not protect the ] inside the character list [].
The correct pattern is then '\[[^]]*\]' as you showed it.

[Updated on: Tue, 01 October 2013 10:51]

Report message to a moderator

Re: Regexp_SUBSTR on an array returning no value [message #597212 is a reply to message #597211]

Tue, 01 October 2013 10:54

caippers
Messages: 6
Registered: October 2013
Location: Des Moines

Junior Member

I"m going to digest all of this. NOOB on REGEXP but appreciate all the help.

Report message to a moderator

Previous Topic:	Need help with UNION while selecting from dual
Next Topic:	PL/SQL for 3 different tables

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 14:37:24 CDT 2024