如何创建将CSV值转换为表的函数，使用正则表达式作为CSV分隔符？

Question

我需要一个通用Oracle函数，它将一个CSV字符串作为第一个参数，一个正则表达式字符串，该函数将CSV分隔符定义为第二个参数，并返回一个解析字符串表，如下所示：

输入数据：

NAME    PROJECT     ERROR
108     test        string-1, string-2 ; string-3
109     test2       single string
110     test3       ab,  ,c

产出数据：

NAME    PROJECT     ERROR
108     test        string-1
108     test        string-2
108     test        string-3
109     test2       single string
110     test3       ab
110     test3       NULL
110     test3       c

在不同的源表中，分隔符可能是不同的，因此我希望能够动态地将它们指定为正则表达式。

如何使用以下代码创建泛型函数：

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;

sql<>fiddle

因此，我正在考虑一个函数，它接受以下参数并返回一个字符串表

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

我用过这个答案

更新：请注意，我在这里使用的缩写"CSV“只是为了解释输入字符串有多个值，用不同的分隔符分隔。我正在处理一个自由的文本，由人类编写，使用不同的分隔符。因此，在我的示例中，输入字符串不必是正确的CSV --它只是一个由多个不同分隔符分隔的字符串。

Answer 1

也许是这样的。根据您的要求，我将它作为PL/SQL函数编写，但请注意，如果输入数据驻留在数据库中，则可以直接在SQL中完成。

为了举例说明，我用默认分隔符调用函数。

如果您不熟悉流水线表函数，可以在文档中阅读它们。

还请注意，在Oracle12.2中，但在12.1中，可以省略table( )操作符--您可以直接“从函数中”选择。

create type str_t as table of varchar2(4000);
/

create or replace function csvstr2tab(
  p_str    in varchar2,
  p_sep_re in varchar2 default '\s*[,;]\s*'
)
  return str_t
  pipelined
as
begin
  for i in 1 .. regexp_count(p_str, p_sep_re) + 1 loop
    pipe row (regexp_substr(p_str, '(.*?)(' || p_sep_re || '|$)', 1, i, null, 1));
  end loop;
  return;
end;
/

select *
from   table(csvstr2tab('blue  ;green,,brown;,yellow;'))
;

COLUMN_VALUE
--------------------
blue
green
[NULL]
brown
[NULL]
yellow
[NULL]

还有一个测试(注意，输出中的第一行也有两个尾随空格)：

select *
from   table(csvstr2tab('blue  ;green,,brown;,yellow;', ';'))
;

COLUMN_VALUE
-----------------
blue  
green,,brown
,yellow

编辑

下面是当输入在表中(例如，由ID标识的行)时，如何使用该函数将输入字符串拆分为令牌，并跟踪令牌顺序。

with
  sample_data(id, str) as (
    select 1201, 'blue  ;green,,brown;,yellow;' from dual union all
    select 1202, 'tinker, tailor, soldier, ...' from dual
  )
select sd.id, sd.str, tf.ord, tf.token
from   sample_data sd,
       lateral ( select rownum as ord, column_value as token
                 from   table(csvstr2tab(sd.str))
               ) tf
order by id, ord
;

    ID STR                             ORD TOKEN   
------ ---------------------------- ------ --------
  1201 blue  ;green,,brown;,yellow;      1 blue    
  1201 blue  ;green,,brown;,yellow;      2 green   
  1201 blue  ;green,,brown;,yellow;      3         
  1201 blue  ;green,,brown;,yellow;      4 brown   
  1201 blue  ;green,,brown;,yellow;      5         
  1201 blue  ;green,,brown;,yellow;      6 yellow  
  1201 blue  ;green,,brown;,yellow;      7         
  1202 tinker, tailor, soldier, ...      1 tinker  
  1202 tinker, tailor, soldier, ...      2 tailor  
  1202 tinker, tailor, soldier, ...      3 soldier  
  1202 tinker, tailor, soldier, ...      4 ...   

Answer 2

试试下面这个可复制的例子。计划的编制：

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)
/

职能执行：

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;
/

函数的使用和结果：

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   

在12.2.0.1.0版上进行了测试。

Answer 3

如果您在数据库中安装了APEX，那么有一个名为STRING.SPLIT的函数可以执行您所需要的功能。您可以传递一个可用于拆分字符串的单个字符或regex表达式。还有一个重载版本的函数，因此可以使用相同的调用来拆分VARCHAR2或CLOB。

WITH
    test_data (NAME, PROJECT, ERROR)
    AS
        (SELECT 108, 'test', 'string-1, string-2 ; string-3' FROM DUAL
         UNION ALL
         SELECT 109, 'test2', 'single string' FROM DUAL
         UNION ALL
         SELECT 110, 'test3', 'ab,  ,c' FROM DUAL)
SELECT name,
       project,
       error,
       TRIM (s.COLUMN_VALUE) as split_value
  FROM test_data td, TABLE (apex_string.split (error, '[,;]')) s;


   NAME    PROJECT                            ERROR      SPLIT_VALUE
_______ __________ ________________________________ ________________
    108 test       string-1, string-2 ; string-3    string-1
    108 test       string-1, string-2 ; string-3    string-2
    108 test       string-1, string-2 ; string-3    string-3
    109 test2      single string                    single string
    110 test3      ab,  ,c                          ab
    110 test3      ab,  ,c
    110 test3      ab,  ,c                          c