SELECT muy complejo

[Casimiro Notevi]

Buen trabajo

[[duilioisola]]

Por si le sirve a alquien:

Verificador de IBAN para Firebird

Código SQL [-]
create or alter procedure verificar_iban (
    iban_a_verificar varchar(100))
returns (
    valido smallint,
    iban varchar(34))
as
declare variable i integer;
declare variable l integer;
declare variable alpha varchar(34);
declare variable numero integer;
begin
    /*
       Devuelve el IBAN limpio (sin espacios ni simbolos).
       Devuelve VALIDO=1 si es correcto.
    */

    -- Inicializo resultado
    valido = 0;
    iban = '';

    -- Limpieza de cuenta (solo ['A'..'Z', '0'..'9'])
    iban_a_verificar = upper(iban_a_verificar);
    i = 1;
    while (i <= char_length(iban_a_verificar)) do
    begin
        if ((substring(iban_a_verificar from i for 1) in ('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J',
                                                          'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T',
                                                          'U', 'V', 'W', 'X', 'Y', 'Z', '0', '1', '2', '3',
                                                          '4', '5', '6', '7', '8', '9'))) then
            iban = iban || substring(iban_a_verificar from i for 1);

        i = i + 1;
    end

    -- Primero el caso obvio de que venga vacio
    if (iban <> '') then
    begin
        -- Paso los primeros 4 digitos al final (Pais + Control)
        iban = substring(iban from 5 for char_length(iban) - 4) || substring(iban from 1 for 4);

        -- Convierto letras a digitos A -> 10, B -> 11, C -> 12, ...
        i = 1;
        while (i <= char_length(iban)) do
        begin
            if (substring(iban from i for 1) = 'A') then
                iban = substring(iban from 1 for i - 1) || '10' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'B') then
                iban = substring(iban from 1 for i - 1) || '11' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'C') then
                iban = substring(iban from 1 for i - 1) || '12' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'D') then
                iban = substring(iban from 1 for i - 1) || '13' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'E') then
                iban = substring(iban from 1 for i - 1) || '14' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'F') then
                iban = substring(iban from 1 for i - 1) || '15' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'G') then
                iban = substring(iban from 1 for i - 1) || '16' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'H') then
                iban = substring(iban from 1 for i - 1) || '17' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'I') then
                iban = substring(iban from 1 for i - 1) || '18' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'J') then
                iban = substring(iban from 1 for i - 1) || '19' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'K') then
                iban = substring(iban from 1 for i - 1) || '20' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'L') then
                iban = substring(iban from 1 for i - 1) || '21' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'M') then
                iban = substring(iban from 1 for i - 1) || '22' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'N') then
                iban = substring(iban from 1 for i - 1) || '23' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'O') then
                iban = substring(iban from 1 for i - 1) || '24' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'P') then
                iban = substring(iban from 1 for i - 1) || '25' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'Q') then
                iban = substring(iban from 1 for i - 1) || '26' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'R') then
                iban = substring(iban from 1 for i - 1) || '27' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'S') then
                iban = substring(iban from 1 for i - 1) || '28' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'T') then
                iban = substring(iban from 1 for i - 1) || '29' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'U') then
                iban = substring(iban from 1 for i - 1) || '30' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'V') then
                iban = substring(iban from 1 for i - 1) || '31' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'W') then
                iban = substring(iban from 1 for i - 1) || '32' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'X') then
                iban = substring(iban from 1 for i - 1) || '33' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'Y') then
                iban = substring(iban from 1 for i - 1) || '34' || substring(iban from i + 1 for char_length(iban) - i);
            if (substring(iban from i for 1) = 'Z') then
                iban = substring(iban from 1 for i - 1) || '35' || substring(iban from i + 1 for char_length(iban) - i);

            i = i + 1;
        end

        -- Calculo de modulo 97 sobre la cuenta
        i = 1;
        l = 9;
        valido = 0;
        alpha = '';

        while (i <= char_length(iban)) do
        begin
            if (l > char_length(iban)) then
                l = char_length(iban);
            alpha = alpha || substring(iban from i for l);
            numero = cast(alpha as integer);
            valido = mod(numero, 97);
            i = i + l;
            alpha = cast(valido as varchar(34));
            l = 9 - char_length(alpha);
        end

        -- Valido si el resultado es 1
        if (valido <> 1) then
            valido = 0;
    end

    suspend;
end

[[duilioisola]]

Puedes verificar la tabla así:

Código SQL [-]
SELECT L.FORPAGO, L.IBAN,
       (SELECT VALIDO
        FROM VERIFICAR_IBAN(L.IBAN)) AS VALIDO
FROM LS01 L
ORDER BY L.IBAN

[Casimiro Notevi]