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
-- 보조 SP, 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
        -- 참고: 여기서 'column unknown: e.i' 오류는 2.5의 이전 빌드에서 발생합니다.
        -- 예: WI-V2.5.2.26540 (Alexey Kovyazin의 편지, 24.08.2014 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;