CREATE or replace FUNCTION MAST_DEV.F_SPLIT
(p_str varchar(8000),p_split varchar(10))
RETURNS table(
result varchar(250)
)
SPECIFIC MAST_DEV.SQL201820135734427
NOT SECURED
LANGUAGE SQL
NOT DETERMINISTIC
EXTERNAL ACTION
READS SQL DATA
INHERIT SPECIAL REGISTERS
return
with n(str, ori, pos) as (
values (p_str||p_split, 1, locate(p_split,p_str))
union all
select str, pos+length(p_split), locate(p_split, str, pos+length(p_split))
from n
where locate(p_split, str, pos+length(p_split))>0)
select substr(str, ori, pos-ori) as result from n
END;
调用:select * from table(MAST_DEV.f_split('1,abc,23,efgh,456',','));
查询结果:
1
abc
23
efgh
456