Validating IBAN in PL/SQL

Question

I'm trying to find some ready-to-use code (yes, I mean teh codez) to validate an IBAN account number in PL/SQL.

Does anyone know about some samples? I think someone should have already implemented that...

Thanks

Answer 1

This one is surely not copyrighted:

declare
as_iban varchar2(34);
ln_iban number(36, 0);
begin
    as_iban := 'enter your IBAN here';

    ln_iban := to_number(substr(as_iban, 5));
    ln_iban := ln_iban * 100 + (ascii(substr(as_iban, 1, 1)) - 55);
    ln_iban := ln_iban * 100 + (ascii(substr(as_iban, 2, 1)) - 55);
    ln_iban := ln_iban * 100 + to_number(substr(as_iban, 3, 2));
    ln_iban := ln_iban mod 97;

    if ln_iban is null or ln_iban <> 1 then 
        raise_application_error(-2e4, 'invalid IBAN: ' || as_iban);
    end if; 
end;
/

Answer 2

A swift Googling throws up an implementation by Alexandre Rodichevski. It's copyrighted so I'm not sure whether it's legal to use it. Anyway, find it here.

Answer 3

Function returns 1 if IBAN is correct and 0 if it's not correct

CREATE OR REPLACE 

    FUNCTION fn_CheckIBAN(
      pIBAN IN VARCHAR2
    ) RETURN INTEGER IS
      lResult     INTEGER;
      IBAN        VARCHAR2(256);
      IBAN_Digits VARCHAR2(256);
      l_mod       NUMBER;
      lTmp        VARCHAR2(8);
      lSCnt       INTEGER := 5;
      i           INTEGER := 1;

---

      FUNCTION fn_GetIBANDigits RETURN VARCHAR2 AS
        lChar   VARCHAR2(1);
        lNumber INTEGER;
        lString VARCHAR2(255);
      BEGIN
        FOR i IN 1..LENGTH(IBAN) LOOP
          lChar := SUBSTR(IBAN, i, 1);
          BEGIN
            lNumber := ASCII(lChar);
            IF lNumber > 47 AND lNumber < 58 THEN
              -- It's number 0 ... 9
              lString := lString || TO_CHAR(lNumber - 48);
            ELSE
              lString := lString || TO_CHAR(lNumber - 55);
            END IF;
          END;
        END LOOP;
        RETURN lString;
      END fn_GetIBANDigits;

---

     BEGIN
      IBAN := SUBSTR(pIBAN, 5) || SUBSTR(pIBAN, 1, 4);

      IBAN_Digits := fn_GetIBANDigits;

      LOOP
        lTmp := SUBSTR(IBAN_Digits, i, lSCnt);
        EXIT WHEN lTmp IS NULL;

        IF l_mod IS NULL THEN
          l_mod := MOD( TO_NUMBER(lTmp), 97);
        ELSE
          l_mod := MOD(TO_NUMBER( TO_CHAR(l_mod) || lTmp), 97);
        END IF;

        i := i + lSCnt;
      END LOOP;

      IF l_mod = 1 THEN
        lResult := 1;
      ELSE
        lResult := 0;
      END IF;

      RETURN(lResult);
    END fn_CheckIBAN;

Answer 4

my modification

CREATE OR REPLACE FUNCTION MOHF.fn_CheckIBAN(
      pIBAN IN VARCHAR2
    ) RETURN varchar2 IS
      lResult     INTEGER;
      IBAN        VARCHAR2(256);
      IBAN_Digits VARCHAR2(256);
      l_mod       NUMBER;
      lTmp        VARCHAR2(8);
      lSCnt       INTEGER := 5;
      i           INTEGER := 1;

---
FUNCTION fn_GetIBANDigits RETURN VARCHAR2 AS
        lChar   VARCHAR2(1);
        lNumber INTEGER;
        lString VARCHAR2(255);
      BEGIN
        FOR i IN 1..LENGTH(IBAN) LOOP
          lChar := SUBSTR(IBAN, i, 1);
          BEGIN
            lNumber := ASCII(lChar);
            IF lNumber > 47 AND lNumber < 58 THEN
              -- It's number 0 ... 9
              lString := lString || TO_CHAR(lNumber - 48);
            ELSE
              lString := lString || TO_CHAR(lNumber - 55);
            END IF;
          END;
        END LOOP;
        RETURN lString; 
        exception  when others then return ( null);
      END fn_GetIBANDigits;

---

     BEGIN
      IBAN := SUBSTR(pIBAN, 5) || SUBSTR(pIBAN, 1, 4);

      IBAN_Digits := fn_GetIBANDigits;

      LOOP
        lTmp := SUBSTR(IBAN_Digits, i, lSCnt);
        EXIT WHEN lTmp IS NULL;

        IF l_mod IS NULL THEN
          l_mod := MOD( TO_NUMBER(lTmp), 97);
        ELSE
          l_mod := MOD(TO_NUMBER( TO_CHAR(l_mod) || lTmp), 97);
        END IF;

        i := i + lSCnt;
      END LOOP;

      IF l_mod = 1 THEN
        lResult := 1;
      ELSE
        lResult := 0;
      END IF;

      RETURN(lResult);
      exception  when others then return ( IBAN); 
    END fn_CheckIBAN;
/