Home » SQL & PL/SQL » SQL & PL/SQL » Parsing query (Oracle 11g)
Parsing query [message #685171] Mon, 01 November 2021 07:53 Go to next message
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!
Re: Parsing query [message #685172 is a reply to message #685171] Mon, 01 November 2021 08:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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.
Re: Parsing query [message #685173 is a reply to message #685172] Mon, 01 November 2021 08:35 Go to previous message
akull
Messages: 46
Registered: July 2012
Location: Argentina
Member
Thanks a lot Sir!!
Previous Topic: Data Dictionary for Collection Bind variables
Next Topic: Oracle stored procedure with complex conditions
Goto Forum:
  


Current Time: Thu Mar 28 16:12:59 CDT 2024