Oracle - CSV splitting only one column in a query

Question

Oracle 12cR1 - I have a complex business process I am putting into a query. In general, the process will be

with t1 as (select CATEGORY, PRODUCT from ... )
select <some manipulation> from t1;

t1 -aka the output of the first line- will look like this:

CATEGORY       PRODUCT
Database       Oracle, MS SQL Server, DB2
Language       C, Java, Python

I need the 2nd line of the SQL query (aka the manipulation) to keep the CATEGORY column, and to split the PRODUCT column on the comma. The output needs to look like this

CATEGORY    PRODUCT
Database    Oracle
Database    MS SQL Server
Database    DB2
Language    C
Language    Java
Language    Python

I have looked at a couple of different CSV splitting options. I cannot use the DBMS_Utility.comma_to_Table function as this has restrictions with special characters or starting with numbers. I found a nice TABLE function which will convert a string to separate rows, called f_convert. This function is on StackOverflow about 1/3 the way down the page here.

Since this is a table function, it is called like so...And will give me 3 rows, as expected.

SELECT * FROM TABLE(f_convert('Oracle, MS SQL Server, DB2'));

How do I treat this TABLE function as it is was a "column function"? Although this is totally improper SQL, I am looking for something like

with t1 as (select CATEGORY, PRODUCT from ... )
select CATEGORY from T1, TABLE(f_convert(PRODUCT) as PRODUCT from t1;

Any help appreciated...

This post may be helpful - https://stackoverflow.com/a/35602396/7613110 — Connor, Nov 02 '17 at 15:45

score 1 · Accepted Answer · answered Nov 02 '17 at 15:56

Use connect by to "loop" through the elements of the list where a comma-space is the delimiter. regexp_substr gets the list elements (the regex allows for NULL list elements) and the prior clauses keep the categories straight.

with t1(category, product) as (
  select 'Database', 'Oracle, MS SQL Server, DB2' from dual union all
  select 'Language', 'C, Java, Python' from dual
)
select category, 
       regexp_substr(product, '(.*?)(, |$)', 1, level, NULL, 1) product
from t1
connect by level <= regexp_count(product, ', ')+1   
  and prior category = category
  and prior sys_guid() is not null; 


CATEGORY PRODUCT
-------- --------------------------
Database Oracle
Database MS SQL Server
Database DB2
Language C
Language Java
Language Python

6 rows selected.

SQL>

Oracle - CSV splitting only one column in a query

1 Answers1