Firebird存储过程：使用分隔符将行拆分为记录集

这是每个使用Firebird PSQL的开发人员迟早都会遇到的任务之一–如何将包含分隔符值的行拆分为独立值的数组？类似这样：

Code


select * from sp_split_into_words('foo rio bar firebird,mysql,postgresql;mssql;redis/rty');

输出：
######

WORD
==========
foo
rio
bar
firebird
mysql
postgresql
mssql
redis
rty

使用纯SQL即可实现–请使用以下存储过程代码，作者是Pavel Zotov，Firebird QA工程师兼IBSurgeon首席DBA。

请注意，此过程包含广泛的分隔符列表，包括空格，因此如果您只需要逗号或双点，可以从变量a_del中移除其他分隔符。

另外，请注意返回varchar的大小（返回变量word）–默认情况下为varchar(50)，您可以根据需要调整。

Code

set term ^;
create or alter procedure sp_split_into_words(
    a_text varchar(2048) character set utf8,
    a_dels varchar(20) default ',.<>/?;:''"[]{}`~!@#$%^&*()-_=+\|/',
    a_special char(1) default ' '
)
returns (
  word varchar(50)
) as
begin
-- 辅助存储过程，仅用于oltp_data_filling.sql中填充表PATTERNS
-- 使用各种单词组合用于SIMILAR TO测试。
for
    with recursive
    j as( -- 循环#1：将分隔符列表转换为行
        select s,1 i, substring(s from 1 for 1) del
        from(
          select replace(:a_dels,:a_special,'') s
          from rdb$database
        )

        UNION ALL

        select s, i+1, substring(s from i+1 for 1)
        from j
        where substring(s from i+1 for 1)<>''
    )

    ,d as(
        select :a_text s, :a_special sp from rdb$database
    )
    ,e as( -- 循环#2：将每个分隔符替换为`空格`
        select d.s, replace(d.s, j.del, :a_special) s1, j.i, j.del
        from d join j on j.i=1

        UNION ALL

        select e.s, replace(e.s1, j.del, :a_special) s1, j.i, j.del
        from e
        -- 注意：在2.5的旧版本上会出现'column unknown: e.i'错误，
        -- 例如：WI-V2.5.2.26540（来自Alexey Kovyazin的信件，2014年8月24日14:34）
        join j on j.i = e.i + 1
    )
    ,f as(
        select s1 from e order by i desc rows 1
    )

    ,r as ( -- 循环#3：将文本拆分为单个单词
        select iif(t.k>0, substring(t.s from t.k+1 ), t.s) s,
             iif(t.k>0,position( del, substring(t.s from t.k+1 )),-1) k,
             t.i,
             t.del,
             iif(t.k>0,left(t.s, t.k-1),t.s) word
        from(
          select f.s1 s, d.sp del, position(d.sp, s1) k, 0 i from f cross join d
        )t

        UNION ALL

        select iif(r.k>0, substring(r.s from r.k+1 ), r.s) s,
             iif(r.k>0,position(r.del, substring(r.s from r.k+1 )),-1) k,
             r.i+1,
             r.del,
             iif(r.k>0,left(r.s, r.k-1),r.s) word
        from r
        where r.k>=0
    )
    select word from r where word>''
    into
        word
do
    suspend;
end
^
set term ;^
commit;