OraFAQ Forum: SQL & PL/SQL » regular expression substring to get the string that is delimited by a comma

Home » SQL & PL/SQL » SQL & PL/SQL » regular expression substring to get the string that is delimited by a comma (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0)

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

regular expression substring to get the string that is delimited by a comma [message #672134]

Wed, 03 October 2018 14:59

wtolentino
Messages: 373
Registered: March 2005

Senior Member

i am attempting to use a regular expression substring function to get the string that is separated by a comma. the code that I have appears to be not getting the complete string when the string is made of multiple string (before or in-between the comma). for example PACKAGE BODY should return as PACKAGE BODY not only as PACKAGE because they are one string.

this is the code I have:

SQL> select level,
  2         regexp_substr(nObjType,'[[:alnum:]]+', 1, level) ObjType
  3   from (select regexp_count(vt1.nObjType,',') + 1 cnt,
  4                nObjType
  5          from (select 'PACKAGE, FUNCTION, "PACKAGE BODY"' nObjType from dual) vt1) vt2
  6  connect by level <=  vt2.cnt;

     LEVEL OBJTYPE
---------- ---------------------------------
         1 PACKAGE
         2 FUNCTION
         3 PACKAGE

SQL>

expected output

     LEVEL OBJTYPE
---------- ---------------------------------
         1 PACKAGE
         2 FUNCTION
         3 PACKAGE BODY

please help.

thank you.

Report message to a moderator

Re: regular expression substring to get the string that is delimited by a comma [message #672135 is a reply to message #672134]

Wed, 03 October 2018 15:39

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

select level,
regexp_substr(nObjType,'[^,]+', 1, level) ObjType
from (select regexp_count(vt1.nObjType,',') + 1 cnt,
nObjType
from (select 'PACKAGE, FUNCTION, PACKAGE BODY' nObjType from dual) vt1) vt2
connect by level <= vt2.cnt;

Report message to a moderator

Re: regular expression substring to get the string that is delimited by a comma [message #672150 is a reply to message #672135]

Thu, 04 October 2018 06:54

wtolentino
Messages: 373
Registered: March 2005

Senior Member

thanks so much that works I only had to add the trim function to remove any whitespace before and after.

SQL> select level,
  2         trim(regexp_substr(nObjType,'[^,]+', 1, level)) ObjType
  3    from (select regexp_count(vt1.nObjType,',') + 1 cnt,
  4                 nObjType
  5            from (select 'PACKAGE, FUNCTION, PACKAGE BODY' nObjType from dual) vt1) vt2
  6  connect by level <= vt2.cnt;

     LEVEL OBJTYPE
---------- -------------------------------
         1 PACKAGE
         2 FUNCTION
         3 PACKAGE BODY

SQL>

Report message to a moderator

Previous Topic:	Creating a function
Next Topic:	If the sting value more then 3 separated need to split into 2nd line

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 02 03:25:42 CDT 2023