OraFAQ Forum: SQL & PL/SQL » Need Explanation on this query

Home » SQL & PL/SQL » SQL & PL/SQL » Need Explanation on this query (Oracle SQL)

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

Need Explanation on this query [message #644932]

Fri, 20 November 2015 04:23

hharishh86
Messages: 2
Registered: November 2015
Location: CHENNAI

Junior Member

Hi,

I am new to Oracle Regex Function. I am trying to understand how Regex works .
Need your help on this .
Thanks in Advance.

Input : 'SMITH,ALLEN,WARD,JONES'

The query executed and output :

SQL> select regexp_substr('SMITH,ALLEN,WARD,JONES','[^,]+', 1, level) from dual
2 connect by regexp_substr('SMITH,ALLEN,WARD,JONES', '[^,]+', 1, level) is not null;

REGEXP_SUBSTR('SMITH,A
----------------------
SMITH
ALLEN
WARD
JONES

I need to know how this works and also the use of LEVEL and Connect by clause .
Also please help me with good regex tutorials if possible

Thanks,
Harish

Report message to a moderator

Re: Need Explanation on this query [message #644934 is a reply to message #644932]

Fri, 20 November 2015 04:56

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

Senior Member
Account Moderator

There are 2 subjects: regexp and hierarchical (CONNECT BY) query.
You have to study one subject independently to the other one.

Regular expression are not an Oracle thing, you'll find many tutorials on the web about this point.
Hierarchical queries are an Oracle stuff, you can start with the Oracle documentation:
Hierarchical Queries
Hierarchical Query Operators
Hierarchical Query Pseudocolumns
Hierarchical Functions

Report message to a moderator

Re: Need Explanation on this query [message #644973 is a reply to message #644934]

Mon, 23 November 2015 01:02

hharishh86
Messages: 2
Registered: November 2015
Location: CHENNAI

Junior Member

Thanks a lot Michel .

I went through the documentation . Now am able to understand how regexp works .

Report message to a moderator

Previous Topic:	SYS_GUID FUNCTION RETURNING SAME VALUE
Next Topic:	Convert Rows to Columns

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 03:30:48 CDT 2024