Manipolare stringhe in una stored procedure

(Firebird v1.5 dispone della funzione SUBSTRING; Firebird v2.0 di TRIM, BIT_LENGTH, CHAR_LENGHT, OCTET_LENGTH).

L'autore dell'articolo è Ivan Prenosil. L'originale, in inglese, è reperibile all'indirizzo: Ivan Prenosil's Firebird/InterBase site.

TrimRight
Troncare una stringa
Lunghezza di una stringa
Posizione di una sottostringa

Tutte queste funzioni possono essere facilmente realizzate via UDF. Bisogna però considerare che:

le funzioni UDF non sono supportate da NetWare;
le librerie UDF dipendono dalla piattaforma;
la mancanza di una libreria UDF può impedire il "restore" di un database;
una funzione UDF non dispone di informazioni sul set di caratteri di una stringa;
una funzione UDF non può restituire NULL;
funzioni UDF scorrette potrebbero bloccare il server.

TrimRight

InterBase non dispone di una funzione “nativa” per tagliare gli spazi bianchi (utile, ad esempio, per la conversione da CHAR a VARCHAR durante l'importazione di dati da un file esterno). La funzione rtrim fa parte della libreria standard ib_udf.dll ma, come si è detto, ci potrebbero esser ragioni per evitare le UDF. È possibile realizzare una funzione rtrim, tranne che nel caso di stringhe molto lunghe, per mezzo del linguaggio delle stored procedure. Il CAST ad una stringa di lunghezza minore ha successo se i caratteri rimossi sono spazi, altrimenti viene sollevata un'eccezione. Possiamo, dunque, effettuare il casting a stringhe di lunghezza via via minore, intercettando eventuali errori. Il codice seguente può essere utilizzato sia come stored procedure (EXECUTE PROCEDURE TrimRight 'abc') che come select procedure (SELECT … FROM TrimRight('abc')):

      CREATE PROCEDURE TrimRight (str VARCHAR(10))
        RETURNS (ret VARCHAR(10)) AS
      BEGIN
        ret = str;
        IF (str IS NULL) THEN BEGIN SUSPEND; EXIT; END
        IF (str = '')    THEN BEGIN ret = ''; SUSPEND; EXIT; END
        BEGIN
          ret = CAST (str AS char(9));
          ret = CAST (str AS char(8));
          ret = CAST (str AS char(7));
          ret = CAST (str AS char(6));
          ret = CAST (str AS char(5));
          ret = CAST (str AS char(4));
          ret = CAST (str AS char(3));
          ret = CAST (str AS char(2));
          ret = CAST (str AS char(1));
          SUSPEND;
          WHEN ANY DO SUSPEND;
        END
      END

Esempi:

      SELECT '>' || ret || '<'
        FROM TrimRight (null)
      ============
      <null>


      SELECT '>' || ret || '<'
        FROM TrimRight (' 1234      ')
      ============
      > 1234<


      EXECUTE PROCEDURE TrimRight '1234      '
      ==========
      1234

È possibile richiamare il codice da una seconda stored procedure:

      EXECUTE PROCEDURE TrimRight str_in
              RETURNING_VALUES    str_out;

Note

Non è possibile utilizzare un ciclo WHILE (…) DO perché l'operatore di cast non ammette una variabile nella posizione che specifica la lunghezza (i.e. CAST(str AS CHAR(:len)) genera un errore).

Troncare una stringa

Una funzione per troncare una stringa deve rimuovere anche caratteri diversi dallo spazio. Il CAST da una stringa ad una più corta solleva l'eccezione "… string truncation". Lo stesso avviene copiando una stringa più lunga in una più breve, però la copia avrà comunque luogo! Tutto quel che dobbiamo fare è intercettare l'eccezione per mezzo di un WHEN ANY DO. Ecco un esempio di procedura per troncare una stringa a 5 caratteri:

      CREATE PROCEDURE Trunc10To5 (a varchar(10))
        RETURNS (ret varchar(5)) AS
      BEGIN
        ret = '';
        ret = a;
        WHEN ANY DO EXIT;
      END

Il comando:

      EXECUTE PROCEDURE Trunc10To5 '1234567890'

Restituisce '12345'.
Notare che non bisogna utilizzare CAST e che la variabile cui si assegna un valore non deve contenere null; infatti queste due procedure NON funzionano:

      CREATE PROCEDURE test1 (a varchar(10))
        RETURNS (ret varchar(5)) AS
      BEGIN
        ret = null;
        ret = a;
        WHEN ANY DO EXIT;
      END

      CREATE PROCEDURE test2 (a varchar(10))
        RETURNS (ret varchar(5)) AS
      BEGIN
        ret = CAST(a AS VARCHAR(5));
        WHEN ANY DO EXIT;
      END

Il fatto che la copia abbia luogo anche in presenza di un'eccezione è probabilmente un bug; comunque questo è il modo di procedere di IB4, IB5 ed IB6.

Lunghezza di una stringa

Dato che una funzione per misurare la lunghezza di una stringa non modifica il suo input, la realizzazione usando WHILE e LIKE è abbastanza immediata:

      CREATE PROCEDURE Len (str VARCHAR(100))
        RETURNS (len INTEGER) AS
      DECLARE VARIABLE pat VARCHAR(100);
      BEGIN
        len = null;
        IF (str IS NULL) THEN EXIT;

        pat = '';
        len = 0;
        WHILE (NOT str LIKE pat) DO BEGIN
          pat = pat || '_';
          len = len + 1;
        END
      END

Le variabili vengono automaticamente inizializzate a null, quindi si può omettere l'assegnamento len = null. La lunghezza calcolata includerà eventuali spazi bianchi a fine stringa.

      EXECUTE PROCEDURE Len null
          LEN 
      ======= 
       <null>

      EXECUTE PROCEDURE Len ''
          LEN 
      ======= 
            0 

      EXECUTE PROCEDURE Len 'abc'
          LEN 
      ======= 
            3 

      EXECUTE PROCEDURE Len 'xyz   '
          LEN 
      ======= 
            6

Posizione di una sottostringa

Questa funzione restituisce la posizione della prima occorrenza della sottostringa SubStr in una data stringa (Str).

      CREATE PROCEDURE Pos (SubStr VARCHAR(100), Str VARCHAR(100))
        RETURNS (Pos INTEGER) AS
      DECLARE VARIABLE SubStr2 VARCHAR(201); /* 1 + SubStr-lenght + Str-length */
      DECLARE VARIABLE Tmp VARCHAR(100);
      BEGIN
        IF (SubStr IS NULL OR Str IS NULL)
        THEN BEGIN Pos = NULL; EXIT; END

        SubStr2 = SubStr || '%';
        Tmp = '';
        Pos = 1;
        WHILE (Str NOT LIKE SubStr2 AND Str NOT LIKE Tmp) DO BEGIN
          SubStr2 = '_' || SubStr2;
          Tmp = Tmp || '_';
          Pos = Pos + 1;
        END

        IF (Str LIKE Tmp) THEN Pos = 0;
      END

La variabile Tmp è usata per interrompere il loop quando il numero di iterazioni raggiunge la lunghezza di Str. Poiché SubStr viene utilizzata alla destra dell'operatore LIKE, non dovrebbe contenere SQL wildcard (i.e. "_" "%"). Nel caso non ci siano occorrenze della sottostringa, viene restituito il valore zero.

      EXECUTE PROCEDURE Pos 'ab', 'abcdefghij'
          POS 
      ======= 
            1 

      EXECUTE PROCEDURE Pos 'cd', 'abcdefghij'
          POS 
      ======= 
            3 

      EXECUTE PROCEDURE Pos 'x', 'abcdefghij'
          POS 
      ======= 
            0