OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Parsing query (Oracle 11g)

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

Parsing query [message #685171]

Mon, 01 November 2021 07:53

akull
Messages: 46
Registered: July 2012
Location: Argentina

Member

Hello Experts!

I need you help in the following parsing query. I got an input from the user. Let's say:

Input: 21-09941_1w_room_new

So, in order to split it using the underscore as a delimiter I wrote the following.

SELECT REGEXP_SUBSTR('21-09941_1_W_room_new', '[^_]+', 1, LEVEL) AS data
   FROM dual
CONNECT BY REGEXP_SUBSTR('21-09941_1_W_room_new', '[^_]+', 1, LEVEL) IS NOT NULL;

My main problem is that I have to split only up to the third underscore, meaning my result should look like.

21-09941
1
W
room_new

Can someone give me a hint on this?

Best Regards!

Report message to a moderator

Re: Parsing query [message #685172 is a reply to message #685171]

Mon, 01 November 2021 08:33

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

Senior Member

SELECT CASE LEVEL
         WHEN 4 THEN SUBSTR('21-09941_1_W_room_new',INSTR('21-09941_1_W_room_new','_',1,3) + 1)
         ELSE REGEXP_SUBSTR('21-09941_1_W_room_new', '[^_]+', 1, LEVEL)
       END AS data
   FROM dual
CONNECT BY LEVEL <= LEAST(4,REGEXP_COUNT('21-09941_1_W_room_new','_') + 1)
/

DATA
--------------------------------------------------------------------------------
21-09941
1
W
room_new

SQL>

SY.

Report message to a moderator

Re: Parsing query [message #685173 is a reply to message #685172]

Mon, 01 November 2021 08:35

akull
Messages: 46
Registered: July 2012
Location: Argentina

Member

Thanks a lot Sir!!

Report message to a moderator

Previous Topic:	Data Dictionary for Collection Bind variables
Next Topic:	Oracle stored procedure with complex conditions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 30 00:24:27 CDT 2023