UFN_VALIDATE_ACCOUNTNUMBER

create function dbo.UFN_VALIDATE_ACCOUNTNUMBER
(
    @FINANCIALINSTITUTIONID uniqueidentifier,
    @ACCOUNTNUMBER nvarchar(50)
)
returns bit
with execute as caller
as 
begin
    /* IBAN validation algorithm
      1)Check that the total IBAN length is correct as per the country. If not, the IBAN is invalid
      2)Move the four initial characters to the end of the string
      3)Replace each letter in the string with two digits, thereby expanding the string, where A = 10, B = 11, ..., Z = 35
      4)Interpret the string as a decimal integer and compute the remainder of that number on division by 97
    */
    declare @CONDITIONSETTINGNAME nvarchar(25);
    select
        @CONDITIONSETTINGNAME = BANKINGSYSTEM.CONDITIONSETTINGNAME
    from
        dbo.BANKINGSYSTEM
        inner join dbo.FINANCIALINSTITUTION on FINANCIALINSTITUTION.BANKINGSYSTEMID = BANKINGSYSTEM.ID
    where
        FINANCIALINSTITUTION.ID = @FINANCIALINSTITUTIONID;

    --ACCOUNTNUMBER is IBAN, do IBAN validation

    --Other (non-SEPA) banking systems don't have validation for ACCOUNTNUMBER

    if @CONDITIONSETTINGNAME = 'BankingSystem-SEPA'
    begin
        declare @COUNTRYCODELENGTH int = 2;
        declare @COUNTRYCODEANDCHECKDIGITSLENGTH int = 4;
        declare @MAXLENGTH int = 34;

        if len(@ACCOUNTNUMBER) <= @COUNTRYCODEANDCHECKDIGITSLENGTH or len(@ACCOUNTNUMBER) >= @MAXLENGTH
            --invalid length

            return 0;

        -- move first 4 characters to end per IBAN validation algorithms

        declare @IBAN nvarchar(50) = substring(@ACCOUNTNUMBER, @COUNTRYCODEANDCHECKDIGITSLENGTH + 1, len(@ACCOUNTNUMBER)) + substring(@ACCOUNTNUMBER, 1, @COUNTRYCODEANDCHECKDIGITSLENGTH);
        declare @INDEX int = 1;
        declare @NEWIBAN nvarchar(20) = '';
        declare @CHAR nvarchar(1);

        --replace letters with 2 numbers

        while @INDEX <= len(@IBAN)
        begin
            set @CHAR = substring(@IBAN, @INDEX, 1);
            if @CHAR like '[0-9]'
            begin
                set @NEWIBAN = @NEWIBAN + @CHAR;
            end
            else if @CHAR like '[a-z]' or @CHAR like '[A-Z]'
            begin
                set @NEWIBAN = @NEWIBAN + convert(nvarchar, ascii(upper(@CHAR))-55);
            end
            else
            begin
                --invalid character

                return 0;
            end
            if len(@NEWIBAN) > 16
            begin
                set @NEWIBAN = convert(nvarchar(20), convert(bigint, @NEWIBAN) % 97);
            end
            set @index = @index + 1;
        end

        if (convert(bigint, @NEWIBAN) % 97) != 1
            --invalid digits

            return 0;

        declare @COUNTRYCODE nvarchar(2) = substring(@ACCOUNTNUMBER, 1, @COUNTRYCODELENGTH);
        declare @IBANSTRUCTURE nvarchar(50) = '';

        select
            @IBANSTRUCTURE = substring(IBANREGISTRY.IBANSTRUCTURE, @COUNTRYCODELENGTH + 1, len(IBANREGISTRY.IBANSTRUCTURE))
        from
            dbo.IBANREGISTRY
        where
            IBANREGISTRY.COUNTRYISO3166 = @COUNTRYCODE;

        if @IBANSTRUCTURE = ''
            --invalid country code

            return 0;

        set @IBAN = substring(@ACCOUNTNUMBER, @COUNTRYCODELENGTH + 1, len(@ACCOUNTNUMBER));

        declare @CURRENTINDEXMAX int;
        declare @CURRENTTYPE nvarchar(1);

        --use IBAN structure in database to validate

        while len(@IBANSTRUCTURE) > 0
        begin
                if substring(@IBANSTRUCTURE, 2, 1) = '!'
                begin
                    set @CURRENTINDEXMAX = convert(int, substring(@IBANSTRUCTURE, 1, 1)) + 1;
                    set @CURRENTTYPE = substring(@IBANSTRUCTURE, 3, 1);
                    set @IBANSTRUCTURE = substring(@IBANSTRUCTURE, 4, len(@IBANSTRUCTURE));
                end
                else if substring(@IBANSTRUCTURE, 3, 1) = '!'
                begin
                    set @CURRENTINDEXMAX = convert(int, substring(@IBANSTRUCTURE, 1, 2)) + 1;
                    set @CURRENTTYPE = substring(@IBANSTRUCTURE, 4, 1);
                    set @IBANSTRUCTURE = substring(@IBANSTRUCTURE, 5, len(@IBANSTRUCTURE));
                end
                else
                begin
                    --invalid ibanstructure

                    return 0;
                end
          if @CURRENTTYPE = 'a'
            begin
                      while @INDEX < @CURRENTINDEXMAX
                      begin
                          if substring(@IBAN, @INDEX, 1) not like '[A-Z]'
                          begin
                              --iban doesn't match structure

                              return 0;
                          end
                          set @INDEX = @INDEX + 1;
                      end
                  end
                else if @CURRENTTYPE = 'n'
                begin
                    while @INDEX < @CURRENTINDEXMAX
                    begin
                        if substring(@IBAN, @INDEX, 1) not like '[0-9]'
                        begin
                            --iban doesn't match structure

                            return 0;
                        end
                        set @INDEX = @INDEX + 1;
                    end
                end
                else if @CURRENTTYPE = 'c'
                begin
                    while @INDEX < @CURRENTINDEXMAX
                    begin
                        if substring(@IBAN, @INDEX, 1) not like '[0-9]' and substring(@IBAN, @INDEX, 1) not like '[A-Z]'  and substring(@IBAN, @INDEX, 1) not like '[a-z]' 
                        begin
                            --iban doesn't match structure

                            return 0;
                        end
                        set @INDEX = @INDEX + 1;
                    end
                end
                else
                begin
                    --invalid ibanstructure

                    return 0;
                end
              set @IBAN = substring(@IBAN, @CURRENTINDEXMAX, len(@IBAN));
              set @INDEX = 1;
        end

        if LEN(@IBAN) > 0
            --iban doesn't match structure

            return 0;
    end

    return 1;

end