Firebird opgeslagen procedure om een regel met scheidingstekens te splitsen naar een recordset

Dit is een van de taken waar elke ontwikkelaar op Firebird PSQL vroeg of laat mee te maken krijgt - hoe splits je een regel die waarden met scheidingstekens bevat naar een array van afzonderlijke waarden? Zoiets als dit:

Code


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

Uitvoer:
######

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

Het is mogelijk om dit puur in SQL te doen - gebruik de volgende stored procedure code, de auteur is Pavel Zotov, Firebird QA engineer en IBSurgeon lead DBA.

Houd er rekening mee dat deze procedure een brede lijst van mogelijke scheidingstekens bevat, inclusief spatie, dus als je alleen een komma of dubbele punt nodig hebt, kun je andere scheidingstekens verwijderen uit de variabele a_del.

Let ook op de grootte van de teruggegeven varchar (return variabele word) - standaard is dit varchar(50), je kunt dit aanpassen aan je behoeften.

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
-- Hulp SP, alleen gebruikt in oltp_data_filling.sql om tabel PATTERNS te vullen
-- met willekeurige combinaties van woorden die gebruikt worden in SIMILAR TO testen.
for
    with recursive
    j as( -- lus #1: transformeer lijst van scheidingstekens naar rijen
        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( -- lus #2: voer vervanging van elk scheidingsteken naar `spatie` uit
        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: hier zal 'column unknown: e.i' fout optreden op oude builds van 2.5,
        -- bijv: WI-V2.5.2.26540 (brief van 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 ( -- lus #3: voer splitsing van tekst in afzonderlijke woorden uit
        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;