Procedimento armazenado Firebird para dividir linha com delimitadores em um conjunto de registros

Esta é uma das tarefas que todo desenvolvedor em Firebird PSQL enfrenta mais cedo ou mais tarde - como dividir uma linha que contém valores com delimitadores em um array de valores separados? Algo assim:

Code


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

Saída:
######

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

É possível fazer isso em SQL puro - por favor, use o seguinte código de procedimento armazenado, o autor é Pavel Zotov, engenheiro de QA da Firebird e DBA líder da IBSurgeon.

Observe que este procedimento contém uma ampla lista de possíveis delimitadores, incluindo espaço, então se você precisar apenas de vírgula ou ponto duplo, você pode remover outros delimitadores da variável a_del.

Também, preste atenção ao tamanho do varchar de retorno (variável de retorno word) - por padrão, é varchar(50), você pode ajustá-lo de acordo com suas necessidades.

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 auxiliar, usada apenas em oltp_data_filling.sql para preencher a tabela PATTERNS
-- com combinações variadas de palavras para serem usadas em testes SIMILAR TO.
for
    with recursive
    j as( -- loop #1: transformar lista de delimitadores em linhas
        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( -- loop #2: realizar substituição de cada delimitador por `espaço`
        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: aqui o erro 'column unknown: e.i' ocorrerá em versões antigas do 2.5,
        -- ex: WI-V2.5.2.26540 (carta de 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 ( -- loop #3: realizar divisão do texto em palavras individuais
        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;