Parse Values with Delimiters Using REGEXP_SUPSTR in Oracle 10g

Question

I have a table called TVL_DETAIL that contains column TVL_CD_LIST. Column TVL_CD_LIST contains three records:
TVL_CD_LIST:
M1180_Z6827
K5900_Z6828
I2510

I've used the following code in an attempt to return the values only(so excluding the underscore):

SELECT
TVL_CD_LIST   
FROM TVL_DETAIL  
WHERE TVL_CD_LIST IN (SELECT regexp_substr(TVL_CD_LIST,'[^_]+', 1, level) FROM DUAL
CONNECT BY regexp_substr(TVL_CD_LIST,'[^_]+', 1, level) IS NOT NULL)

What I was expecting to see returned in separate rows was:
M1180
Z6827
K5900
Z6828
I2510

But it only returns I2510(which is the original value that doesn't contain an underscore).

What am I doing wrong? Any help is appreciated. Thanks!

score 0 · Answer 1 · edited May 23 '17 at 11:45

To answer your question, you are querying for the list where it matches a sub-element and that will only happen where the list is comprised of one element. What you really wanted to select are the sub-elements themselves.

Note: Explanation of why parsing strings using the regex form '[^_]+' is bad here: https://stackoverflow.com/a/31464699/2543416

You want to parse the list, selecting the elements:

SQL> with TVL_DETAIL(TVL_CD_LIST) as (
     select 'M1180_Z6827' from dual union
     select 'K5900_Z6828' from dual union
     select 'I2510' from dual
   )
   SELECT distinct regexp_substr(TVL_CD_LIST, '(.*?)(_|$)', 1, level, NULL, 1) element
   FROM TVL_DETAIL
   CONNECT BY level <= LENGTH(regexp_replace(TVL_CD_LIST, '[^_]', '')) + 1;
   -- 11g  CONNECT BY level <= regexp_count(TVL_CD_LIST, '_') + 1;

ELEMENT
-----------
Z6827
K5900
M1180
I2510
Z6828

SQL>

And this is cool if you want to track by row and element within row:

SQL> with TVL_DETAIL(row_nbr, TVL_CD_LIST) as (
     select 1, 'M1180_Z6827' from dual union
     select 2, 'K5900_Z6828' from dual union
     select 3, 'I2510' from dual
   )
   SELECT row_nbr, column_value substring_nbr,
          regexp_substr(TVL_CD_LIST, '(.*?)(_|$)', 1, column_value, NULL, 1) element
   FROM TVL_DETAIL,
     TABLE(
       CAST(
         MULTISET(SELECT LEVEL
                  FROM dual
                  CONNECT BY level <= LENGTH(regexp_replace(TVL_CD_LIST, '[^_]', '')) + 1
                  -- 11g CONNECT BY LEVEL <= REGEXP_COUNT(TVL_CD_LIST, '_')+1
                  ) AS sys.OdciNumberList
           )
     )
   order by row_nbr, substring_nbr;

   ROW_NBR SUBSTRING_NBR ELEMENT
---------- ------------- -----------
         1             1 M1180
         1             2 Z6827
         2             1 K5900
         2             2 Z6828
         3             1 I2510

SQL>

EDIT: Oops, edited to work with 10g as REGEXP_COUNT is not available until 11g.

Thanks Gary_W...so when I run the query, I get an this error: ORA-00939: too many arguments for function — user3666552, Dec 14 '16 at 19:45
Check your syntax carefully, as you can see it worked for me. — Gary_W, Dec 14 '16 at 21:42

score 0 · Answer 2 · answered Dec 08 '16 at 09:06

The query you have used creates the list but you are comparing the list of record with column it self using the in clause, as such M1180 or Z6827 cannot be equal to M1180_Z6827 and so for K5900_Z6828. I2510 has only one value so it gets matched.

You can use below query if your requirement is exactly what you have mentioned in your desired output.

SQL> WITH tvl_detail AS
      2   (SELECT 'M1180_Z6827' tvl_cd_list FROM dual
      3    UNION ALL
      4    SELECT 'K5900_Z6828' FROM dual
      5    UNION ALL
      6    SELECT 'I2510' FROM dual)
      7   ---------------------------
      8   --- End of data preparation
      9   ---------------------------
     10  SELECT regexp_substr(tvl_cd_list, '[^_]+', 1, LEVEL) AS tvl_cd_list
     11    FROM tvl_detail
     12  CONNECT BY regexp_substr(tvl_cd_list, '[^_]+', 1, LEVEL) IS NOT NULL
     13         AND PRIOR tvl_cd_list = tvl_cd_list
     14         AND PRIOR sys_guid() IS NOT NULL;

OUTPUT:

TVL_CD_LIST
--------------------------------------------
I2510
K5900
Z6828
M1180
Z6827

Parse Values with Delimiters Using REGEXP_SUPSTR in Oracle 10g

2 Answers2