Procédure stockée Firebird pour diviser une ligne avec des délimiteurs en ensemble d'enregistrements

C’est l’une des tâches auxquelles tout développeur sur Firebird PSQL est confronté tôt ou tard - comment diviser une ligne contenant des valeurs avec des délimiteurs en un tableau de valeurs séparées ? Quelque chose comme ceci :

Code


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

Sortie :
######

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

Il est possible de le faire en SQL pur - veuillez utiliser le code de procédure stockée suivant, dont l’auteur est Pavel Zotov, ingénieur QA Firebird et DBA principal chez IBSurgeon.

Veuillez noter que cette procédure contient une large liste de délimiteurs possibles, y compris l’espace, donc si vous n’avez besoin que d’une virgule ou d’un point-virgule, vous pouvez supprimer les autres délimiteurs de la variable a_del.

De plus, faites attention à la taille du varchar retourné (variable de retour word) - par défaut, c’est varchar(50), vous pouvez l’ajuster selon vos besoins.

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
-- Procédure auxiliaire, utilisée uniquement dans oltp_data_filling.sql pour remplir la table PATTERNS
-- avec des combinaisons aléatoires de mots à utiliser dans les tests SIMILAR TO.
for
    with recursive
    j as( -- boucle #1 : transformer la liste des délimiteurs en lignes
        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( -- boucle #2 : effectuer le remplacement de chaque délimiteur par `espace`
        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 : ici, l'erreur 'column unknown: e.i' se produira sur les anciennes versions de 2.5,
        -- par exemple : WI-V2.5.2.26540 (lettre d'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 ( -- boucle #3 : effectuer la division du texte en mots individuels
        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;