Thủ tục lưu trữ Firebird để tách dòng có dấu phân cách thành tập bản ghi

Đây là một trong những tác vụ mà mọi lập trình viên trên Firebird PSQL sớm hay muộn đều phải đối mặt - làm thế nào để tách một dòng chứa các giá trị có dấu phân cách thành một mảng các giá trị riêng biệt? Ví dụ như thế này:

Code


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

Kết quả:
######

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

Có thể thực hiện điều này bằng SQL thuần túy - vui lòng sử dụng mã thủ tục lưu trữ sau đây, tác giả là Pavel Zotov, kỹ sư QA Firebird và DBA chính của IBSurgeon.

Xin lưu ý rằng thủ tục này chứa một danh sách rộng các dấu phân cách có thể có, bao gồm cả khoảng trắng, vì vậy nếu bạn chỉ cần dấu phẩy hoặc dấu chấm, bạn có thể xóa các dấu phân cách khác khỏi biến a_del.

Ngoài ra, hãy chú ý đến kích thước của varchar trả về (biến trả về word) - theo mặc định, nó là varchar(50), bạn có thể điều chỉnh theo nhu cầu của mình.

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 phụ trợ, chỉ được sử dụng trong oltp_data_filling.sql để điền bảng PATTERNS
-- với các tổ hợp từ khác nhau để sử dụng trong kiểm thử SIMILAR TO.
for
    with recursive
    j as( -- vòng lặp #1: chuyển đổi danh sách dấu phân cách thành các hàng
        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( -- vòng lặp #2: thực hiện thay thế từng dấu phân cách bằng `khoảng trắng`
        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
        -- nb: ở đây sẽ có lỗi 'column unknown: e.i' trên các bản dựng cũ của 2.5,
        -- ví dụ: WI-V2.5.2.26540 (thư từ 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 ( -- vòng lặp #3: thực hiện tách văn bản thành các từ riêng lẻ
        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;