sql – Create a function that turns a single CSV row into a table using a regular expression to find delimiters

Question:

Please tell me how to change the following code :

with temp as
(
    select 108 Name, 'test' Project, 'string-1 , string-2 ; string-3' Error  from dual
    union all
    select 109, 'test2', 'single string' from dual
)
select distinct
  t.name, t.project,
  trim(regexp_substr(t.error, '[^,;]+', 1, levels.column_value))  as error
from 
  temp t,
  table(cast(multiset(select level from dual connect by  level <= length (regexp_replace(t.error, '[^,;]+'))  + 1) as sys.OdciNumberList)) levels
order by name;

into a universal function that takes a string as parameters (example: 'string--1, string-2 ; string-3 , string-4' ) and a regular expression that specifies the search for a string separator (example: '\s*[,;]\s*' ):

FUNCTION csvstr2tab(
    p_str      IN VARCHAR2,
    p_sep_re   IN VARCHAR2   DEFAULT '\s*[,;]\s*'
)
...

As an attempt of my own, here is a function that accomplishes the given task using a fixed line separator ( ',' ):

create or replace type admin.strtable as table of varchar2(1000);
/

create or replace
function admin.str2tbl ( p_str in varchar2 ) return strtable
as
  l_str long default p_str || ',';
  l_n number;
    l_data strtable := strtable();
begin
  loop
    l_n := instr( l_str, ',' );
    exit when (nvl(l_n,0) = 0);
    l_data.extend;
    l_data( l_data.count ) := ltrim(rtrim(substr(l_str,1,l_n-1)));
    l_str := substr( l_str, l_n+1 );
  end loop;
  return l_data;
end;
/

P.S. sql<>fiddle

UPDATE: I used the abbreviation CSV just to show that I'm dealing with strings that contain delimiter-separated substrings. In fact, the input strings are not necessarily "valid" CSVs at all.

Answer:

Try the reproducible example below (tested on version 12.2.0.1.0).

Schema preparation:

create table prjerr as
    select 108 Name, 'test' Project, 'string-1 , string-2 ; string-3' Error  from dual
    union all
    select 109, 'test2', 'single string' from dual
/
create or replace type tokenList is table of varchar2 (32767)
/

The function itself:

create or replace function csvstr2tab (
        str varchar2, delimiter char := '\s*[,;]\s*') return tokenList is
    pattern constant varchar2 (64) := '(.*?)(('||delimiter||')|($))';
    tokens tokenList := tokenList ();
    s varchar2 (96);
    c int := 0;
begin 
    <<split>> loop c := c + 1;  
        s := regexp_substr (str, pattern, 1, c, null, 1);
        exit split when s is null; 
        tokens.extend;
        tokens(tokens.last) := s;
    end loop;
    return tokens;
end csvstr2tab;
/

Request and result:

select distinct name, project, t.column_value error
from prjerr p, csvstr2tab (p.error) t 
order by name
/
      NAME PROJE ERROR           
---------- ----- ----------------
       108 test  string-1        
       108 test  string-2        
       108 test  string-3        
       109 test2 single string
Post Views: 8
Scroll to Top