Validare IBAN

Configurare noua (How To)

Situatie

Pentru cazurile in care avem nevoie de validarea unui IBAN, am creat o procedura ce face acest lucru.

Procedura are doi parametri:

  • primul parametru este un varchar ce reprezinta iban-ul de verificat si validat
  • al doilea parametru este un out de tip int, ce returneaza 1 daca IBAN-ul este valid sau 0 daca IBAN-ul este invalid.

Solutie

/*
declare @IsValid int
declare @Cont varchar(200)
declare @NodId int
exec BD_Verificare_IBAN ‘AICI SE PUNE IBANUL DE VERIFICAT’, @IsValid out
select @IsValid

*/

ALTER proc BD_Verificare_IBAN
@IBANAccount varchar(60),
@IsValid int out

as
set nocount on
declare @Ccy varchar(3), @sAccount varchar(100),@nAccount varchar(100), @SS varchar(2), @CC varchar(10), @TT varchar(2), @AA varchar(2), @BIC varchar(4), @RO varchar(2), @CControl varchar(2)
declare @decAccount decimal(38,0), @Conv varchar(2)
declare @i int, @len int, @cAccount varchar(100)
declare @BIC_ext varchar(2);
declare @strintN varchar(100)
if datalength(LTRIM(RTRIM(@IBANAccount)))<=4
begin
select @IsValid = 0
return 0
end

select @IBANAccount = replace(@IBANAccount, ‘ ‘, ”)
select @IBANAccount = replace(@IBANAccount, ‘.’, ”)
select @IBANAccount = replace(@IBANAccount, ‘_’, ”)
select @IBANAccount = replace(@IBANAccount, ‘-‘, ”)
select @IBANAccount = replace(@IBANAccount, ‘/’, ”)

select @IBANAccount = upper(@IBANAccount)

–RO –59
select @RO = substring(@IBANAccount,1,2), @CControl = substring(@IBANAccount,3,2)
set @BIC_ext = substring (@IBANAccount,9,2); –00

if(isnumeric(@BIC_ext) = 1 )
begin
select @BIC = substring(@IBANAccount,5,4)

end
else
begin
select @BIC = substring(@IBANAccount,5,6)

end
select @sAccount = substring(@IBANAccount,5,datalength(@IBANAccount)-4)+@RO+@CControl

if(len(@IBANAccount)>10)
if len(@IBANAccount)<24
begin

select @IsValid = 0
, @cAccount = ‘-1’
return 0
end

create table #ConvLetter(FromChar char(1), ToInt int)
select @i = 0
while @i<= 26
begin
insert #ConvLetter select char(65+@i), 10+@i
select @i = @i+1
end

select @i = 1, @nAccount = ”

while @i<=datalength(@sAccount)
begin
select @Conv = substring(@sAccount,@i, 1)
if isnumeric(@Conv) = 0
select @Conv = convert(char(2), ToInt) from #ConvLetter where FromChar=@Conv
else
select @Conv = ltrim(rtrim(@Conv))
select @nAccount = @nAccount+@Conv
select @i=@i+1
end

select @cAccount = @nAccount

Tip solutie

Permanent
Etichetare:

Voteaza

(21 din 51 persoane apreciaza acest articol)

Despre Autor

Leave A Comment?