Splitting Comma Separated values into columns oracle

Question

I built this solution based on a similar question here, but my example is much bigger than the example cited there. My solution works, but I'm wondering if it's the best/most efficient.

One caveat: I need this to be able to run just as a straight query.

I built this SQL to split a string into multiple columns. I left my variable in there because:

1. it's shorter than the real string and
2. it doesn't really matter for asking the question.

At runtime, that variable is replaced with a string with 14 values separated by 13 commas. I need the last 3 values concatenated together.

Without further ado, here's my query:

select  
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  1)   c1, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  2)   c2, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  3)   c3, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  4)   c4, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  5)   c5, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  6)   c6, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  7)   c7, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  8)   c8, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1,  9)   c9, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1, 10)  c10, 
            regexp_substr('$CSV Text Single Line$','[^,]+',1, 11)  c11, 
    replace(regexp_substr('$CSV Text Single Line$','[^,]+',1, 12)  
            ||','|| 
            regexp_substr('$CSV Text Single Line$','[^,]+',1, 13)  
            ||','|| 
            regexp_substr('$CSV Text Single Line$','[^,]+',1, 14) 
            ,'"','')                                               c12 
from dual 

Thanks in advance for any advice.

Answer 1

The ugly part is that the CSV string appears more than once in the query. An improvement I see is to isolate that string in a subquery:

with
  csv as (
    select '$CSV Text Single Line$' str 
      from dual)
select regexp_substr(str,'[^,]+', 1,  1)   c1, 
       regexp_substr(str,'[^,]+', 1,  2)   c2, 
       regexp_substr(str,'[^,]+', 1,  6)   c6, 
       regexp_substr(str,'[^,]+', 1,  7)   c7, 
       regexp_substr(str,'[^,]+', 1,  8)   c8, 
       regexp_substr(str,'[^,]+', 1,  9)   c9, 
       regexp_substr(str,'[^,]+', 1, 10)  c10, 
       regexp_substr(str,'[^,]+', 1, 11)  c11, 
       replace(regexp_substr(str,'[^,]+', 1, 12)  ||','|| 
          regexp_substr(str,'[^,]+', 1, 13)  ||','|| 
          regexp_substr(str,'[^,]+', 1, 14) ,'"','') c12 
from csv;

In case of a long CSV string you may save valuable space in your shared pool area, especially if you execute this query quite often using different hard-coded CSV strings. If binds are used, the advantage is that it's enough to bind one variable instead of 11.

Answer 2

Regular expressions can be expensive. For your last column, instead of using 3 regex functions and then concatenating them, you can use simple SUBSTR.

select replace(
               substr('$CSV Text Single Line$', 
                      instr('$CSV Text Single Line$',',',-1,3) + 1
                     ),
              '"',''
              ) as c12
from dual;