BSN 11 test

create or replace FUNCTION is_string_a_valid_bsn(inBsnChar in varchar2) RETURN NUMBER IS

-- Yes is BSN = True = 0

-- NO not BSN = False  <> 0

val NUMBER;

mul NUMBER;

bsn NUMBER;

BEGIN

  bsn:=regexp_substr(inBsnChar,'^[[:digit:]]{8,9}$');

  if bsn is null then

    return 98;

  end if ;

  bsn:=to_number(trim(inBsnChar));

  val := 11 - (bsn mod 10); -- the check digit

  mul := 1;

  WHILE (bsn > 0) loop

     bsn := FLOOR (bsn / 10);

     mul := mul + 1;

     val := val + (bsn mod 10) * mul;

  END LOOP;

  IF (mul < 9) THEN

     RETURN 13;

  END IF; -- too short

  RETURN val mod 11;

  EXCEPTION when others then

    RETURN 99;

END;