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)
regular expression substring to get the string that is delimited by a comma [message #672134] Wed, 03 October 2018 14:59 Go to next message
wtolentino
Messages: 271
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.
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 Go to previous messageGo to next message
pablolee
Messages: 2880
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;
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 Go to previous message
wtolentino
Messages: 271
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>
Previous Topic: Creating a function
Next Topic: If the sting value more then 3 separated need to split into 2nd line
Goto Forum:
  


Current Time: Thu Jul 18 14:34:41 CDT 2019