Разбиение строки по разделителю в SQL и PL/SQL имеет несколько решений. Каждое решение имеет право на жизнь, и его применение зависит от многих факторов: настроек БД, объема данных, состояния таблицы, индексов, партиций, контекста поставленной задачи, необходимости делать выбор между SQL и PL/SQL, и т.д.
В некотором смысле этот пост коллекционный.
Самый распространенный и часто применяющийся способ - это разбиение с помощью substr, instr и connect by:
-- connect by
with t_row as (select '100,200,300,400,500' || ',' AS value
from dual)
select substr(t.value, decode(level, 1, 1, instr(t.value, ',', 1, level - 1) + 1),
instr(t.value, ',', 1, level) - decode(level, 1, 1, instr(t.value, ',', 1, level - 1) + 1)) as val
from t_row t
connect by nvl(instr(t.value, ',', 1, level), 0) <> 0;
То же самое, но с регулярными выражениями:
-- regexp
with t_row as (select '100,200,300,400,500' || ',' AS value
from dual)
select regexp_substr(t.value, '[^,]+', 1, level) as val
from t_row t
connect by level <= length(regexp_replace(t.value, '[^,]+')) + 1;
Оба варианта могут довольно долго работать со столбцами без индексов и не подходят для больших массивов. Вполне применимы в функциях.
Рекурсивный подход - CTE
- плюсы в материализации и довольно быстром проходе по массиву.
-- regexp by
recursive CTE
with t_row as (select 'Cat;Dog;Horse;Cow' as vvalue from dual
union all
select 'Bee;Spider;Fly;Ant' as vvalue from dual),
t_recursive (id, vvalue, svalue, sepcount) as
(select 0 as id,
vvalue,
vvalue as svalue,
length(regexp_replace('Cat;Dog;Horse;Cow', '[^;]+')) + 1 as sepcount
from t_row
union all
select id + 1 as id,
regexp_substr(t.svalue, '[^;]+', 1, id + 1) as vvalue,
svalue,
sepcount
from t_recursive t
where id < sepcount)
select *
from t_recursive
where id <> 0;
Если требуется разделить не только на строки, но и на столбцы:
-- split to rows
and columns
with t_row as (select '100,200,300;400,500,600' || ',' AS value
from dual)
select
regexp_substr(str, '[^,]+', 1, 1) as str1,
regexp_substr(str, '[^,]+', 1, 2) as str2,
regexp_substr(str, '[^,]+', 1, 3) as str3
from (select regexp_substr(str, '[^;]+', 1, level) str
from (select rtrim(value,';') as str
from t_row)
connect by regexp_instr(str, ';', 1, level - 1) > 0
);
Разбор на лексемы для нескольких строк:
with t as (select 'a;б;в' as str, 1 as id from dual
union all
select 'г;д;' as str,2 from dual
union all
select null as str, 3 from dual
union all
select 'p' as str, 4 from dual
)
select
regexp_substr(str, '[^;]+', 1, level) as str2,
t.*
from t
connect by instr(trim(';' from str), ';', 1, level - 1) > 0
and prior id
= id
and prior
dbms_random.value is not null;
APEX_STRING.SPLIT
Если установлен APEX, есть готовая функция в пакете APEX_STRING:
split (
p_str in varchar2,
p_sep in varchar2 default apex_application.LF,
p_limit in pls_integer default null )
return
apex_t_varchar2;
select * from table(apex_string.split('100,200,300,400,500',','));
APEX_UTIL.STRING_TO_TABLE
Есть еще одна функция APEX, считается deprecated, но удобна тем, что работает с CLOB, в отличие от apex_string.split. Для разбиения больших объемов эффективнее создать конвеерную функцию.Объявим тип (или используем свои типы):
create or replace type t_varchar_object as object (val varchar2(4000));
create or replace type t_list as table of t_varchar_object;
Создадим функцию:
create or replace function fnc_split_apex(str in clob,ch in varchar2) return t_list
pipelined
is
v_path APEX_APPLICATION_GLOBAL.VC_ARR2;
begin
v_path := APEX_UTIL.STRING_TO_TABLE(str, ch);
for i in v_path.first..v_path.last
loop
pipe row (t_varchar_object(v_path(i)));
end loop;
exception
when others
then null;
end;
Еще одна конвеерная функция, которая возвращает коллецию.
Объявим тип
(или используем свои типы):
create or replace type split_tbl as table of varchar2(32767);
Создадим функцию:
create or replace function split(p_list varchar2,
p_delimiter varchar2 := ',') return split_tbl
pipelined
is
l_idx
pls_integer;
l_list
varchar2(32767) := p_list;
l_value
varchar2(32767);
begin
loop
l_idx := instr(l_list, p_delimiter);
-- exit when nvl(l_idx,0) = 0;
if l_idx > 0
then
pipe row(substr(l_list, 1 ,l_idx - 1));
l_list := substr(l_list, l_idx + length(p_delimiter));
else
pipe row(l_list);
exit;
end if;
end loop;
return;
end split;
select * from table(split('100,200,300,400,500'));
И еще одна конвеерная функция, которая возвращает коллецию.
create or replace function split2(p_list in varchar2,
p_delimiter in varchar2 default ',') return split_tbl
as
splitted split_tbl := split_tbl();
i pls_integer := 0;
l_list varchar2(32767) := p_list;
begin
loop
i := instr(l_list, p_delimiter);
if i > 0
then
splitted.extend(1);
splitted(splitted.last) := substr(l_list, 1, i - 1);
l_list := substr(l_list, i + length(p_delimiter));
else
splitted.extend(1);
splitted(splitted.last) := l_list;
return splitted;
end if;
end loop;
end;
/
select * from table(split2('100,200,300,400,500'));
Кто сказал, что функций бывает много?
create or replace function split3(p_list varchar2,
p_delimiter varchar2 := ',') return split_tbl
is
splitted split_tbl := split_tbl();
begin
bulk collect into splitted
from (select p_delimiter || p_list || p_delimiter as ExpandedList,
instr(p_delimiter || p_list || p_delimiter, p_delimiter, 1, rownum) CommaBefore,
instr(p_delimiter || p_list || p_delimiter, p_delimiter, 1, rownum + 1) CommaAfter
from all_objects
where instr(p_delimiter|| p_list, p_delimiter, 1, rownum) > 0);
return splitted;
end;
select * from table(split3('100,200,300,400,500'));
SQL-Injection 😁 Используем using out для преобразования данных к нужному нам типу
Создаем тип или используем уже созданный:
create or replace type split_tbl_nmbr as table of number;
Создадим функцию:
create or replace function split_number (p_list varchar2) return split_tbl_nmbr
is
v_number_list split_tbl_nmbr;
begin
execute immediate 'BEGIN :1 := split_tbl_nmbr(' || p_list || '); END;' using out v_number_list;
return v_number_list;
end;
select * from table(split_number('100,200,300,400,500'));
XML. Применять в крайних случаях, есть варианты и понадежнее.
select extractvalue(value(x), '/b') x
from table
( xmlsequence
( xmltype
( '<a><b>'
|| replace('100,200,300,400,500', ',', '</b><b>')
|| '</b></a>'
).extract('/*/*')
)
) x;
XMLTable и FLWOR expression.
Этот вариант разбиения использует ora:tokenize XQuery function, появившиеся в Oracle 11.
create or replace function
split_function_pipe (p_list varchar2)
return sys.odcivarchar2list pipelined as
begin
for i in (
select result
from xmltable ('if
(contains($X,",")) then ora:tokenize($X,"\,") else $X' passing p_list as x
columns result varchar2(4000) path '.'
)
)
loop
pipe row (trim(i.result));
end loop;
end;
select * from table(split_function_pipe('100,200,300,400,500'));
COLLECT и ora:tokenize XQuery function.
create or replace function
split_number_token (p_list varchar2) return split_tbl_nmbr
is
v_number_list split_tbl_nmbr;
begin
select cast(collect(x) as split_tbl_nmbr)
into v_number_list
from xmltable('for $i in
ora:tokenize(substring(.,2,string-length(.)-2),"]\[") return
xs:integer($i)'
passing p_list
columns x number path '.') x;
return v_number_list;
end;
select * from table(split_number_token('[100][200][300][400][500]'));
COMMA_TO_TABLE.
У функции comma_to_table есть один недостаток. Она не
работает, если в обрабатываемой строке между разделителями находится число.
Например, если вместо строки 'a1,a2,a3,a4' обрабатывать строку '1,2,3,4', то
возникнет ошибка. Изначально эта процедура предназначена для работы с
Oracle-идентификаторами, а значит, ограничения, налагаемые на нейминг
идентификаторов, также будут относится к результатам разбиения:
- не более 30 символов;
- должны начинаться с буквы;
- не должны содержать спецсимволов и т.д.
Иначе процедура вернет ошибку ORA-00931 missing
identifier.
Но так легко никто не сдается, поэтому создаем
функцию, где вводим дополнительный символ 'a'.
create or replace function
split_comma_to_table (p_list varchar2,
p_delimiter varchar2 := ',')
return split_tbl
pipelined
is
p_tablen binary_integer; -- out
l_tab DBMS_UTILITY.uncl_array;
val varchar2(30);
l_tr varchar2(1);
BEGIN
l_tr := 'a';
DBMS_UTILITY.comma_to_table(list => l_tr||replace(p_list, p_delimiter, ','||l_tr ),
tablen => p_tablen,
tab => l_tab);
FOR i IN 1 .. p_tablen
loop
val := replace(l_tab(i),','||l_tr,',');
val := substr (val, 1 + length(l_tr), length(val) - length(l_tr));
pipe row (val);
end loop;
end;
Проверяем:
select * from table (split_comma_to_table('4,q,ss,as,11,aa_zz223,454aa_zz_ra45'));
Для гурманов через MODEL
with t as (select 'v1' vid,
'Cat;Dog;Horse;Cow' vvalue,
'Animals' comments
from dual)
select vid,
vvalue,
comments
from t
model
return updated rows
dimension by (0 dummy)
measures (vid,
vvalue s,
cast(null as varchar2(20)) vvalue,
comments)
rules (vid[1]=vid[0],
comments[1]=comments[0],
s[for dummy from 1 to regexp_count(s[0], '[^;]+') increment 1] = s[0],
vvalue[for dummy from 1 to regexp_count(s[0], '[^;]+') increment 1] = regexp_substr(s[0],'[^;]+', 1, cv(dummy))
);
На этом все, присылайте свои рецепты.
Источники:
0 Комментарии