DB2将字符串转换成行的函数

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

 

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页