我需要一个通用的Oracle函数，它将CSV字符串作为第一个参数，并将正则表达式字符串定义为第二个参数，然后返回一个解析字符串表，如下所示：
INPUT数据：
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
不同的源表中的分隔符可能不同，所以我希望能够将它们动态指定为regex。
如何从以下代码中创建通用函数：
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<gt；小提琴
所以我想到了一个函数，它接受以下参数，并返回一个字符串表
CREATE OR REPLACE FUNCTION csvstr2tab(
p_str IN VARCHAR2,
p_sep_re IN VARCHAR2 DEFAULT 's*[,;]s*'
)
PS我用过这个答案
更新：请注意，我使用的是缩写"；CSV"；这里只是为了解释输入字符串有多个值，由不同的分隔符分隔。我正在处理一篇由人类撰写的自由文本，它使用了不同的分隔符。因此，在我的情况下，输入字符串不一定是正确的CSV——它只是一个由多个不同分隔符分隔的字符串。
也许是这样的。按照您的要求，我将其作为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 ...
尝试下面的可复制示例。方案编制：
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
PS在12.2.0.1.0版上进行了测试
您可以使用REGEXP_INSTR来跟踪正则表达式匹配的开始和结束，这样在每次迭代时，正则表达式就不需要从字符串的开头重新开始匹配。
CREATE FUNCTION regexp_split(
value IN VARCHAR2,
regexp_separator IN VARCHAR2 DEFAULT ','
) RETURN string_list PIPELINED DETERMINISTIC
AS
position PLS_INTEGER := 1;
next_position PLS_INTEGER;
BEGIN
IF value IS NULL THEN
RETURN;
END IF;
LOOP
next_position := REGEXP_INSTR( value, regexp_separator, position, 1, 0 );
IF next_position = 0 THEN
PIPE ROW ( SUBSTR( value, position ) );
EXIT;
ELSE
PIPE ROW ( SUBSTR( value, position, next_position - position ) );
position := REGEXP_INSTR( value, regexp_separator, next_position, 1, 1 );
END IF;
END LOOP;
RETURN;
END;
/
(注意：您也可以使函数DETERMINISTIC。(
然后，对于测试数据：
CREATE TABLE table_name ( NAME, PROJECT, ERROR ) AS
SELECT 108, 'test1', '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 UNION ALL
SELECT 111, 'test4', '1,2,;5,,,9' FROM DUAL;
您可以使用带有CROSS APPLY(或LATERAL联接(的函数来拆分字符串：
SELECT t.name,
t.project,
s.COLUMN_VALUE AS error
FROM table_name t
CROSS APPLY TABLE( regexp_split( error, 's*[,;]s*' ) ) s
哪个输出：
NAME|PROJECT|ERROR---：|：------|：------------108|test1|string-1108|test1|string-2108|test1|string-3109|test2|单个字符串110|test3|ab110|test3|空110|test3|c111|test4|1111|test4|2111|test4|空111|test4|5111|test4|空111|test4|空111|test4|9
db<gt；小提琴这里
如果您的数据库中安装了APEX，则会有一个名为APEX_STRING.SPLIT的函数来执行您想要的操作。您可以传递一个可用于拆分字符串的单个字符或正则表达式。函数还有一个重载版本，因此可以使用相同的调用来拆分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