Збережена процедура 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 - будь ласка, використовуйте наступний код збереженої процедури, автор - Павло Зотов, інженер із забезпечення якості Firebird та провідний DBA IBSurgeon.

Зверніть увагу, що ця процедура містить широкий список можливих роздільників, включаючи пробіл, тому якщо вам потрібна лише кома або крапка з комою, ви можете видалити інші роздільники зі змінної 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
        -- nb: тут буде помилка 'column unknown: e.i' на старих збірках 2.5,
        -- напр: WI-V2.5.2.26540 (лист від Олексія Ковязіна, 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;