Firebird-Stored-Procedure zum Aufteilen einer Zeile mit Trennzeichen in ein Recordset

Dies ist eine der Aufgaben, mit denen sich jeder Entwickler in Firebird PSQL früher oder später konfrontiert sieht - wie man eine Zeile, die Werte mit Trennzeichen enthält, in ein Array einzelner Werte aufteilt? So etwas wie:

Code


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

Ausgabe:
######

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

Es ist möglich, dies in reinem SQL zu bewerkstelligen - bitte verwenden Sie den folgenden Code der gespeicherten Prozedur, dessen Autor Pavel Zotov ist, Firebird QA-Ingenieur und leitender DBA bei IBSurgeon.

Bitte beachten Sie, dass diese Prozedur eine breite Liste möglicher Trennzeichen enthält, einschließlich Leerzeichen. Wenn Sie also nur ein Komma oder einen Doppelpunkt benötigen, können Sie andere Trennzeichen aus der Variablen a_del entfernen.

Achten Sie auch auf die Größe des zurückgegebenen varchar (Rückgabevariable word) - standardmäßig ist es varchar(50), Sie können es nach Ihren Bedürfnissen anpassen.

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
-- Hilfs-SP, nur in oltp_data_filling.sql verwendet, um die Tabelle PATTERNS
-- mit verschiedenen Kombinationen von Wörtern zu füllen, die beim SIMILAR TO-Test verwendet werden.
for
    with recursive
    j as( -- Schleife #1: Liste der Trennzeichen in Zeilen umwandeln
        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( -- Schleife #2: jedes Trennzeichen durch `Leerzeichen` ersetzen
        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 wird auf alten Builds von 2.5 ein 'column unknown: e.i'-Fehler auftreten,
        -- z.B: WI-V2.5.2.26540 (Brief von 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 ( -- Schleife #3: Text in einzelne Wörter aufteilen
        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;