T-SQL

Re: functie sql care verifica IBAN-ul

rsocol — Wed, 30 Jul 2008 04:25:19 GMT

Da, arată onorabil funcţia (deşi ar putea să arate şi mai bine), însă nu prea funcţionează corect. După câte înţeleg, ar trebui să returneze 1 pentru IBAN-urile corecte sau altă valoare (0 sau mai mare decât 1) pentru cele greşite.

De exemplu:

SELECT dbo.CumVretiVoiSaSeNumeasca('RO28CITI0000000824824444') -- IBAN valid, returnează 1 (corect)
SELECT dbo.CumVretiVoiSaSeNumeasca('RO97BACX0000000030193313') -- IBAN valid, returnează 1 (corect)
SELECT dbo.CumVretiVoiSaSeNumeasca('RO23INGB0001000000000222') -- IBAN valid, returnează 1 (corect)

SELECT dbo.CumVretiVoiSaSeNumeasca('RO28CITI0000000824824443') -- IBAN invalid, returnează 71 (corect)
SELECT dbo.CumVretiVoiSaSeNumeasca('RO28CITI000000082482444') -- IBAN invalid, returnează 0 (corect)
SELECT dbo.CumVretiVoiSaSeNumeasca('000000000000000000000001') -- IBAN invalid, returnează 10000 (corect)

Dar:

SELECT dbo.CumVretiVoiSaSeNumeasca('IT59A0306930542011614440681') -- IBAN valid (din Italia), returnează 0 (greşit)

SELECT dbo.CumVretiVoiSaSeNumeasca('RO26CITI\\00000824824444') -- IBAN invalid, returnează 1 (greşit)

SELECT dbo.CumVretiVoiSaSeNumeasca('RO2800000000000824824444') -- IBAN invalid, dă eroare (greşit)

SELECT dbo.CumVretiVoiSaSeNumeasca('RO28CITI0000000824824445') -- IBAN invalid, se blochează (greşit)

Răzvan

Re: functie sql care verifica IBAN-ul

B_gd_n[ ]Sahlean — Tue, 29 Jul 2008 17:49:57 GMT

Dacă e să fie poezie, dară să fie poezie ...

...
declare @i bigint
declare @char varchar(1)
set @i=1
while @i<len(@NR)
begin
select @char=substring(@NR,@i,1)
if @char in ('0','1','2','3','4','5','6','7','8','9')
begin
set @i=@i+1
continue
end

set @NR=replace(@NR,@char,convert(varchar,ascii(@char)-55))
set @i=@i+1
end
...

||
\/

CREATE TABLE Corepondenta (C CHAR(1) PRIMARY KEY, N CHAR(2) NOT NULL);
INSERT INTO Corepondenta VALUES ('A','10');INSERT INTO Corepondenta VALUES ('B','11');INSERT INTO Corepondenta VALUES ('C','12');...

Având în vedere
declare @NR varchar(100)
atunci secvenţa de cod de mai sus va fi înlocuită de:
UPDATE Corepondeta SET @NR = REPLACE(@NR,C,N)

Vorba poetului: dacă e set oriented atunci să fie set oriented. Există şi niste semne de întrebare.

PS: Există o variantă şi pentru fanii codurilor ASCII

Re: functie sql care verifica IBAN-ul

Alex — Tue, 29 Jul 2008 13:29:21 GMT

am gasit o functie care face lucrul asta...verifica IBAN....si e prea frumoasa ca sa nu v-o arat si voua....(nici nu se compara cu Struto-Camila pe care o facusem eu :) )

create FUNCTION CumVretiVoiSaSeNumeasca
(@IBAN varchar(50))
RETURNS int
AS
BEGIN
if len(@IBAN)!=24
return 0

if substring(@IBAN,1,2)!='RO'

declare @NR varchar(100)
select @NR=substring(@IBAN,5,20)+substring(@IBAN,1,4)

declare @i  bigint
declare @char varchar(1)
set @i=1
while @i<len(@NR)
begin
  select @char=substring(@NR,@i,1)
  if @char in ('0','1','2','3','4','5','6','7','8','9')
  begin
   set @i=@i+1
   continue
  end

set @NR=replace(@NR,@char,convert(varchar,ascii(@char)-55))
set @i=@i+1
end

declare @aux varchar(50)
declare @aux1 varchar(2)
select @aux=substring(@NR,1,2)
if convert(bigint,@aux)<97
set @aux=substring(@NR,1,3)
if convert(bigint,@aux)<97
set @aux=substring(@NR,1,4)

while convert(bigint,@aux)>=97
begin
  set @aux1=convert(varchar,convert(bigint,@aux)%97)
  select @NR=@aux1+substring(@NR,len(@aux)+1,100)
  select @aux=substring(@NR,1,2)
  if @aux not in ('98','99') and len(@NR)<=2
   return convert(int,@NR)%97

while convert(bigint,@aux)<97
set @aux=substring(@NR,1,len(@aux)+1)
end
return convert(int,@NR)
END

alex.

Re: functie sql care verifica IBAN-ul

B_gd_n[ ]Sahlean — Sat, 26 Jul 2008 16:02:33 GMT

CREATE FUNCTION Sushi (@numar VARCHAR(76),@div TINYINT)
RETURNS TINYINT
AS
BEGIN
  DECLARE @numar1 NUMERIC(38)
  DECLARE @numar2 NUMERIC(38)
  DECLARE @auxiliar NUMERIC(38)
  SET @numar1 = SUBSTRING(@numar,1,LEN(@numar)/2)
  SET @numar2 = SUBSTRING(@numar,LEN(@numar)/2+1,76)

  SET @auxiliar = '1'+REPLICATE('0',LEN(@numar2))
  RETURN (((@numar1%@div)*(@auxiliar%@div))%@div+@numar2%@div)%@div
END
GO

SELECT dbo.Sushi('1010101011131007593840000272449',97)
GO

Re: functie sql care verifica IBAN-ul

B_gd_n[ ]Sahlean — Sat, 26 Jul 2008 08:26:26 GMT

@Alex
( ( ( 101010101113100 mod 97 ) * ( 10^16 mod 97 ) ) mod 97 + 7593840000272449 mod 97) mod 97
( (   64                       *   62             ) mod 97 + 10                     ) mod 97
(     88                                                   + 10                     ) mod 97
(     98                                                                            ) mod 97 = 1

+

DRY

Re: functie sql care verifica IBAN-ul

rsocol — Sat, 26 Jul 2008 03:14:45 GMT

Alex:
set @CalcString = left(@WorkingString, 8)
while @CalcString > @Divisor
begin
set @WorkingString = right(@WorkingString, len(@WorkingString) - len(@CalcString))
set @WorkingString = cast((@CalcString % @Divisor) as varchar) + @WorkingString
set @CalcString = left(@WorkingString, 8)
end

Cam ăsta e algoritmul de împărţire de la clasa a 2-a (numai că atunci coborai doar câte o cifră, iar aici poţi să le iei câte 8).

Răzvan

Re: functie sql care verifica IBAN-ul

Alex — Sat, 26 Jul 2008 01:31:59 GMT

am rezolvat pana la urma cu functia asta

create Function BigStringModulo(@BigNumString varchar(500), @Divisor int)
returns int
as
--Function BigStringModulo
--blindman, 1/18/2005
--Returns the Modulo value of a large number expressed as a string value.
--Does not verify that the string is a valid number!

begin
declare @WorkingString as varchar(500)
declare @CalcString as varchar(8)
set @WorkingString = @BigNumString

set @CalcString = left(@WorkingString, 8)
while @CalcString > @Divisor
begin
set @WorkingString = right(@WorkingString, len(@WorkingString) - len(@CalcString))
set @WorkingString = cast((@CalcString % @Divisor) as varchar) + @WorkingString
set @CalcString = left(@WorkingString, 8)
end

return cast(@CalcString as int)
end

multumesc.

alex.

Re: functie sql care verifica IBAN-ul

Alex — Sat, 26 Jul 2008 00:50:26 GMT

nu-i nici o problema...din ce e acolo se prea poate :)...in fine....tot nu-mi iese....nu-mi da rest 1 cum ar trebui...si nu inteleg de ce

le-am tot spart si degeaba...nu inteleg unde gresesc

mai jos e functia....si apoi selectul...daca are cineva timp sa arunce un ochi...poate isi da seama ce nu e corect acolo in ultima parte

multumesc.

create FUNCTION Caracter2Cod( @c CHAR(1) )
RETURNS TINYINT
AS
BEGIN
DECLARE @t TABLE (Caracter CHAR(1), Cod TINYINT)
INSERT INTO @t VALUES ('A',10)
INSERT INTO @t VALUES ('B',11)
INSERT INTO @t VALUES ('C',12)
INSERT INTO @t VALUES ('D',13)
INSERT INTO @t VALUES ('E',14)
INSERT INTO @t VALUES ('F',15)
INSERT INTO @t VALUES ('G',16)
INSERT INTO @t VALUES ('H',17)
INSERT INTO @t VALUES ('I',18)
INSERT INTO @t VALUES ('J',19)
INSERT INTO @t VALUES ('K',20)
INSERT INTO @t VALUES ('L',21)
INSERT INTO @t VALUES ('M',22)
INSERT INTO @t VALUES ('N',23)
INSERT INTO @t VALUES ('O',24)
INSERT INTO @t VALUES ('P',25)
INSERT INTO @t VALUES ('Q',26)
INSERT INTO @t VALUES ('R',27)
INSERT INTO @t VALUES ('S',28)
INSERT INTO @t VALUES ('T',29)
INSERT INTO @t VALUES ('U',30)
INSERT INTO @t VALUES ('V',31)
INSERT INTO @t VALUES ('W',32)
INSERT INTO @t VALUES ('X',33)
INSERT INTO @t VALUES ('Y',34)
INSERT INTO @t VALUES ('Z',35)
RETURN (SELECT Cod FROM @t WHERE Caracter = @c)
END
GO

---------------------------------------------------------------------------------------------------------------------------------------

declare @var varchar (200),
@IBAN1 varchar(2),@IBAN2 varchar(2),@IBAN3 varchar(2),@IBAN4 varchar(2),@IBAN5 varchar(2),@IBAN6 varchar(2),@IBAN7 varchar(2),@IBAN8 varchar(2),
@IBAN9 varchar(2),@IBAN10 varchar(2),@IBAN11 varchar(2),@IBAN12 varchar(2),@IBAN13 varchar(2),@IBAN14 varchar(2),@IBAN15 varchar(2),@IBAN16 varchar(2),
@IBAN17 varchar(2),@IBAN18 varchar(2),@IBAN19 varchar(2),@IBAN20 varchar(2),@IBAN21 varchar(2),@IBAN22 varchar(2),@IBAN23 varchar(2),@IBAN24 varchar(2)

set @var = 'RO49AAAA1B31007593840000'
set @var = right(@var,20) + left(@var,4)
select @var as IBAN

select @IBAN1 =CASE WHEN substring(@var,1,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')
  THEN dbo.Caracter2Cod(substring(@var,1,1))
  ELSE substring(@var,1,1) end
select @IBAN2 = CASE WHEN substring(@var,2,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')
  THEN dbo.Caracter2Cod(substring(@var,2,1))
  ELSE substring(@var,2,1) end
select @IBAN3 = CASE WHEN substring(@var,3,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')
  THEN dbo.Caracter2Cod(substring(@var,3,1))
  ELSE substring(@var,3,1) end
select @IBAN4 = CASE WHEN substring(@var,4,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')
  THEN dbo.Caracter2Cod(substring(@var,4,1))
  ELSE substring(@var,4,1) end
select @IBAN5 = CASE WHEN substring(@var,5,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')
  THEN dbo.Caracter2Cod(substring(@var,5,1))
  ELSE substring(@var,5,1) end
select @IBAN6 = CASE WHEN substring(@var,6,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')
  THEN dbo.Caracter2Cod(substring(@var,6,1))
  ELSE substring(@var,6,1) end
select @IBAN7 = CASE WHEN substring(@var,7,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')
  THEN dbo.Caracter2Cod(substring(@var,7,1))
  ELSE substring(@var,7,1) end
select @IBAN8 = CASE WHEN substring(@var,8,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')
  THEN dbo.Caracter2Cod(substring(@var,8,1))
  ELSE substring(@var,8,1) end
select @IBAN9 = CASE WHEN substring(@var,9,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')
  THEN dbo.Caracter2Cod(substring(@var,9,1))
  ELSE substring(@var,9,1) end
select @IBAN10 = CASE WHEN substring(@var,10,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')
  THEN dbo.Caracter2Cod(substring(@var,10,1))
  ELSE substring(@var,10,1) end
select @IBAN11 = CASE WHEN substring(@var,11,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')
  THEN dbo.Caracter2Cod(substring(@var,11,1))
  ELSE substring(@var,11,1) end
select @IBAN12 = CASE WHEN substring(@var,12,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')
  THEN dbo.Caracter2Cod(substring(@var,12,1))
  ELSE substring(@var,12,1) end
select @IBAN13 = CASE WHEN substring(@var,13,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')
  THEN dbo.Caracter2Cod(substring(@var,13,1))
  ELSE substring(@var,13,1) end
select @IBAN14 = CASE WHEN substring(@var,14,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')
  THEN dbo.Caracter2Cod(substring(@var,14,1))
  ELSE substring(@var,14,1) end
select @IBAN15 = CASE WHEN substring(@var,15,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')
  THEN dbo.Caracter2Cod(substring(@var,15,1))
  ELSE substring(@var,15,1) end
select @IBAN16 = CASE WHEN substring(@var,16,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')
  THEN dbo.Caracter2Cod(substring(@var,16,1))
  ELSE substring(@var,16,1) end
select @IBAN17 = CASE WHEN substring(@var,17,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')
  THEN dbo.Caracter2Cod(substring(@var,17,1))
  ELSE substring(@var,17,1) end
select @IBAN18 = CASE WHEN substring(@var,18,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')
  THEN dbo.Caracter2Cod(substring(@var,18,1))
  ELSE substring(@var,18,1) end
select @IBAN19 = CASE WHEN substring(@var,19,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')
  THEN dbo.Caracter2Cod(substring(@var,19,1))
  ELSE substring(@var,19,1) end
select @IBAN20 = CASE WHEN substring(@var,20,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')
  THEN dbo.Caracter2Cod(substring(@var,20,1))
  ELSE substring(@var,20,1) end
select @IBAN21 = CASE WHEN substring(@var,21,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')
  THEN dbo.Caracter2Cod(substring(@var,21,1))
  ELSE substring(@var,21,1) end
select @IBAN22 = CASE WHEN substring(@var,22,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')
  THEN dbo.Caracter2Cod(substring(@var,22,1))
  ELSE substring(@var,22,1) end
select @IBAN23 = CASE WHEN substring(@var,23,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')
  THEN dbo.Caracter2Cod(substring(@var,23,1))
  ELSE substring(@var,23,1) end
select @IBAN24 = CASE WHEN substring(@var,24,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')
  THEN dbo.Caracter2Cod(substring(@var,24,1))
  ELSE substring(@var,24,1) end

select @IBAN1,@IBAN2,@IBAN3,@IBAN4,@IBAN5,@IBAN6,@IBAN7,@IBAN8,@IBAN9,@IBAN10,@IBAN11,@IBAN12,@IBAN13,
@IBAN14,@IBAN15,@IBAN16,@IBAN17,@IBAN18,@IBAN19,@IBAN20,@IBAN21,@IBAN22,@IBAN23,@IBAN24

declare @FinalIBAN varchar(200)
select @FinalIBAN = @IBAN1+@IBAN2+@IBAN3+@IBAN4+@IBAN5+@IBAN6+@IBAN7+@IBAN8+@IBAN9+@IBAN10+@IBAN11+@IBAN12+@IBAN13+@IBAN14+
@IBAN15+@IBAN16+@IBAN17+@IBAN18+@IBAN19+@IBAN20+@IBAN21+@IBAN22+@IBAN23+@IBAN24
SELECT @FinalIBAN --1010101011131007593840000272449

-- PANA AICI E BINE

DECLARE @FinalIBAN1 VARCHAR(200)
DECLARE @FinalIBAN2 VARCHAR(200)
DECLARE @FinalIBAN3 VARCHAR(200)
DECLARE @FinalIBAN4 VARCHAR(200)
DECLARE @FinalIBAN5 VARCHAR(200)
SELECT @FinalIBAN1 = SUBSTRING(@FinalIBAN,1,8) --10101010
SELECT @FinalIBAN2 = SUBSTRING(@FinalIBAN,9,7) --1113100
SELECT @FinalIBAN3 = SUBSTRING(@FinalIBAN,16,5) --75938
SELECT @FinalIBAN4 = SUBSTRING(@FinalIBAN,21,5) --40000
SELECT @FinalIBAN5 = SUBSTRING(@FinalIBAN,26,6) --272449
SELECT @FinalIBAN1,@FinalIBAN2,@FinalIBAN3,@FinalIBAN4,@FinalIBAN5

SELECT CONVERT(bigint,@FinalIBAN1),CONVERT(bigint,@FinalIBAN2),CONVERT(bigint,@FinalIBAN3),CONVERT(bigint,@FinalIBAN4),CONVERT(bigint,@FinalIBAN5)
select @FinalIBAN1,@FinalIBAN2,@FinalIBAN3,@FinalIBAN4,@FinalIBAN5

DECLARE @REST bigint
SELECT @REST = (@FinalIBAN1*10^23 + @FinalIBAN2*10^16 + @FinalIBAN3*10^11 + @FinalIBAN4*10^6 +@FinalIBAN5) % 97

--SELECT @REST = ((@FinalIBAN1*10^23 + @FinalIBAN2*10^16 + @FinalIBAN3*10^11 + @FinalIBAN4*10^6) +@FinalIBAN5) % 97
--SELECT @REST = (((@FinalIBAN1*10^23 + @FinalIBAN2*10^16 + @FinalIBAN3*10^11 + @FinalIBAN4*10^6)%97) + (@FinalIBAN5%97)) % 97
--SELECT @REST = (((((((@FinalIBAN1%97)*(10^23)%97)%97 + ((@FinalIBAN2%97)*(10^16)%97)%97)%97) + (((@FinalIBAN3%97)*(10^11)%97)%97 + ((@FinalIBAN4%97)*(10^6)%97)%97)%97)%97) + (@FinalIBAN5%97)) % 97

--SELECT @REST = (((@FinalIBAN1*10^23)%97) + ((@FinalIBAN2*10^16)%97) + ((@FinalIBAN3*10^11)%97) + ((@FinalIBAN4*10^6)%97) +(@FinalIBAN5%97))%97
--SELECT @REST = ((((@FinalIBAN1%97)*(10^23)%97)%97) + (((@FinalIBAN2%97)*(10^16)%97)%97) + (((@FinalIBAN3%97)*(10^11)%97)%97) + (((@FinalIBAN4%97)*(10^6)%97)%97) +(@FinalIBAN5%97))%97
--SELECT @REST = (((((@FinalIBAN1%97)*(10^23)%97)%97)%97) + ((((@FinalIBAN2%97)*(10^16)%97)%97)%97) + ((((@FinalIBAN3%97)*(10^11)%97)%97)%97) + ((((@FinalIBAN4%97)*(10^6)%97)%97)%97) +((@FinalIBAN5%97)%97))%97
select @REST as REST

--n * m mod p = ((n mod p) * (m mod p)) mod p

--n + m mod p = ((n mod p) + (m mod p)) mod p

Re: functie sql care verifica IBAN-ul

B_gd_n[ ]Sahlean — Fri, 25 Jul 2008 21:36:19 GMT

@Alex: nu te supăra dar am impresia că eşti masochist.
Transformarea caracterelor în coduri se poate realiza folosind o funcţia (de exemplu):
CREATE FUNCTION Caracter2Cod( @c CHAR(1) )
RETURNS TINYINT
AS
BEGIN
  DECLARE @t TABLE (Caracter CHAR(1), Cod TINYINT)
  INSERT INTO @t VALUES ('A',10)
  INSERT INTO @t VALUES ('B',11)
  INSERT INTO @t VALUES ('C',12)
...
...
...
  RETURN (SELECT Cod FROM @t WHERE Caracter = @c)
END
Apel:
SELECT dbo.Caracter2Cod( 'B' )

Poţi simplifica implementarea funcţiei Caracter2Cod plecând de la codul ASCII al caracterului.

Pentru a calcula modulo folosind numere mari ai expuse aici două metode (atenţie la teste).
Exemplu: foloseşti formulele
n * m mod p = ((n mod p) * (m mod p)) mod p

n + m mod p = ((n mod p) + (m mod p)) mod p

1010101011131007593840000272449 => 101010101113100*10^LEN('7593840000272449') + 7593840000272449
1010101011131007593840000272449 => 101010101113100*10^16 + 7593840000272449
adică
((101010101113100*10^16 mod 97) + (7593840000272449 mod 97)) mod 97
Şi aici 101010101113100*10^16 mod 97 aplici n * m mod p = ((n mod p) * (m mod p)) mod p .

Referitor la IBAN

Re: functie sql care verifica IBAN-ul

Alex — Fri, 25 Jul 2008 21:02:21 GMT

chiar nu-mi dau seama cum ma poate ajuta impartirea de clasa a 2-a in situatia de fata :) ....imi poti da mai multe amanunte....multumesc.

alex.

Re: functie sql care verifica IBAN-ul

rsocol — Fri, 25 Jul 2008 13:45:10 GMT

Având în vedere că nu există în SQL Server un tip de date care să poată prelucra exact un număr având până la 48 de cifre (sau chiar 68, având în vedere că în alte ţări IBAN-ul poate avea până la 34 de caractere), va fi nevoie să îţi aminteşti cum se făcea împărţirea la clasa a 2-a şi să implementezi algoritmul respectiv. Eu am făcut acest lucru, dar nu în T-SQL, ci în alt limbaj.

Răzvan

Re: functie sql care verifica IBAN-ul

Alex — Fri, 25 Jul 2008 13:36:16 GMT

chiar nu stiu cum sa impart la 97...cum sa convertesc ca sa pot face modulo (%)

are cineva vreo idee....

-- daca il puneti intr-un QA nu e cine stie ce o sa vedeti (am facut marea prostie sa impart IBAN-ul in 24 de caractere..asa am stiut eu sa convertesc fiecare bucatica din el)

declare @var varchar (200),
@IBAN1 varchar(2),
@IBAN2 varchar(2),
@IBAN3 varchar(2),
@IBAN4 varchar(2),
@IBAN5 varchar(2),
@IBAN6 varchar(2),
@IBAN7 varchar(2),
@IBAN8 varchar(2),
@IBAN9 varchar(2),
@IBAN10 varchar(2),
@IBAN11 varchar(2),
@IBAN12 varchar(2),
@IBAN13 varchar(2),
@IBAN14 varchar(2),
@IBAN15 varchar(2),
@IBAN16 varchar(2),
@IBAN17 varchar(2),
@IBAN18 varchar(2),
@IBAN19 varchar(2),
@IBAN20 varchar(2),
@IBAN21 varchar(2),
@IBAN22 varchar(2),
@IBAN23 varchar(2),
@IBAN24 varchar(2)

set @var = 'RO49AAAA1B31007593840000'
set @var = right(@var,20) + left(@var,4)
select @var as IBAN

select @IBAN1 = case when substring(@var,1,1) = 'A' then '10 '
   when substring(@var,1,1) = 'B' then '11'
   when substring(@var,1,1) = 'C' then '12'
   when substring(@var,1,1) = 'D' then '13'
   when substring(@var,1,1) = 'E' then '14'
   when substring(@var,1,1) = 'F' then '15'
   when substring(@var,1,1) = 'G' then '16'
   when substring(@var,1,1) = 'H' then '17'
   when substring(@var,1,1) = 'I' then '18'
   when substring(@var,1,1) = 'J' then '19'
   when substring(@var,1,1) = 'K' then '20'
   when substring(@var,1,1) = 'L' then '21'
   when substring(@var,1,1) = 'M' then '22'
   when substring(@var,1,1) = 'N' then '23'
   when substring(@var,1,1) = 'O' then '24'
   when substring(@var,1,1) = 'P' then '25'
   when substring(@var,1,1) = 'Q' then '26'
   when substring(@var,1,1) = 'R' then '27'
   when substring(@var,1,1) = 'S' then '28'
   when substring(@var,1,1) = 'T' then '29'
   when substring(@var,1,1) = 'U' then '30'
   when substring(@var,1,1) = 'V' then '31'
   when substring(@var,1,1) = 'W' then '32'
   when substring(@var,1,1) = 'X' then '33'
   when substring(@var,1,1) = 'Y' then '34'
   when substring(@var,1,1) = 'Z' then '35'
   ELSE substring(@var,1,1)
  end,
@IBAN2 = case when substring(@var,2,1) = 'A' then '10 '
   when substring(@var,2,1) = 'B' then '11'
   when substring(@var,2,1) = 'C' then '12'
   when substring(@var,2,1) = 'D' then '13'
   when substring(@var,2,1) = 'E' then '14'
   when substring(@var,2,1) = 'F' then '15'
   when substring(@var,2,1) = 'G' then '16'
   when substring(@var,2,1) = 'H' then '17'
   when substring(@var,2,1) = 'I' then '18'
   when substring(@var,2,1) = 'J' then '19'
   when substring(@var,2,1) = 'K' then '20'
   when substring(@var,2,1) = 'L' then '21'
   when substring(@var,2,1) = 'M' then '22'
   when substring(@var,2,1) = 'N' then '23'
   when substring(@var,2,1) = 'O' then '24'
   when substring(@var,2,1) = 'P' then '25'
   when substring(@var,2,1) = 'Q' then '26'
   when substring(@var,2,1) = 'R' then '27'
   when substring(@var,2,1) = 'S' then '28'
   when substring(@var,2,1) = 'T' then '29'
   when substring(@var,2,1) = 'U' then '30'
   when substring(@var,2,1) = 'V' then '31'
   when substring(@var,2,1) = 'W' then '32'
   when substring(@var,2,1) = 'X' then '33'
   when substring(@var,2,1) = 'Y' then '34'
   when substring(@var,2,1) = 'Z' then '35'
   ELSE substring(@var,2,1)
  end,
@IBAN3 = case when substring(@var,3,1) = 'A' then '10 '
   when substring(@var,3,1) = 'B' then '11'
   when substring(@var,3,1) = 'C' then '12'
   when substring(@var,3,1) = 'D' then '13'
   when substring(@var,3,1) = 'E' then '14'
   when substring(@var,3,1) = 'F' then '15'
   when substring(@var,3,1) = 'G' then '16'
   when substring(@var,3,1) = 'H' then '17'
   when substring(@var,3,1) = 'I' then '18'
   when substring(@var,3,1) = 'J' then '19'
   when substring(@var,3,1) = 'K' then '20'
   when substring(@var,3,1) = 'L' then '21'
   when substring(@var,3,1) = 'M' then '22'
   when substring(@var,3,1) = 'N' then '23'
   when substring(@var,3,1) = 'O' then '24'
   when substring(@var,3,1) = 'P' then '25'
   when substring(@var,3,1) = 'Q' then '26'
   when substring(@var,3,1) = 'R' then '27'
   when substring(@var,3,1) = 'S' then '28'
   when substring(@var,3,1) = 'T' then '29'
   when substring(@var,3,1) = 'U' then '30'
   when substring(@var,3,1) = 'V' then '31'
   when substring(@var,3,1) = 'W' then '32'
   when substring(@var,3,1) = 'X' then '33'
   when substring(@var,3,1) = 'Y' then '34'
   when substring(@var,3,1) = 'Z' then '35'
   ELSE substring(@var,3,1)
  end,
@IBAN4 = case when substring(@var,4,1) = 'A' then '10 '
   when substring(@var,4,1) = 'B' then '11'
   when substring(@var,4,1) = 'C' then '12'
   when substring(@var,4,1) = 'D' then '13'
   when substring(@var,4,1) = 'E' then '14'
   when substring(@var,4,1) = 'F' then '15'
   when substring(@var,4,1) = 'G' then '16'
   when substring(@var,4,1) = 'H' then '17'
   when substring(@var,4,1) = 'I' then '18'
   when substring(@var,4,1) = 'J' then '19'
   when substring(@var,4,1) = 'K' then '20'
   when substring(@var,4,1) = 'L' then '21'
   when substring(@var,4,1) = 'M' then '22'
   when substring(@var,4,1) = 'N' then '23'
   when substring(@var,4,1) = 'O' then '24'
   when substring(@var,4,1) = 'P' then '25'
   when substring(@var,4,1) = 'Q' then '26'
   when substring(@var,4,1) = 'R' then '27'
   when substring(@var,4,1) = 'S' then '28'
   when substring(@var,4,1) = 'T' then '29'
   when substring(@var,4,1) = 'U' then '30'
   when substring(@var,4,1) = 'V' then '31'
   when substring(@var,4,1) = 'W' then '32'
   when substring(@var,4,1) = 'X' then '33'
   when substring(@var,4,1) = 'Y' then '34'
   when substring(@var,4,1) = 'Z' then '35'
   ELSE substring(@var,4,1)
  end,
@IBAN5 = case when substring(@var,5,1) = 'A' then '10 '
   when substring(@var,5,1) = 'B' then '11'
   when substring(@var,5,1) = 'C' then '12'
   when substring(@var,5,1) = 'D' then '13'
   when substring(@var,5,1) = 'E' then '14'
   when substring(@var,5,1) = 'F' then '15'
   when substring(@var,5,1) = 'G' then '16'
   when substring(@var,5,1) = 'H' then '17'
   when substring(@var,5,1) = 'I' then '18'
   when substring(@var,5,1) = 'J' then '19'
   when substring(@var,5,1) = 'K' then '20'
   when substring(@var,5,1) = 'L' then '21'
   when substring(@var,5,1) = 'M' then '22'
   when substring(@var,5,1) = 'N' then '23'
   when substring(@var,5,1) = 'O' then '24'
   when substring(@var,5,1) = 'P' then '25'
   when substring(@var,5,1) = 'Q' then '26'
   when substring(@var,5,1) = 'R' then '27'
   when substring(@var,5,1) = 'S' then '28'
   when substring(@var,5,1) = 'T' then '29'
   when substring(@var,5,1) = 'U' then '30'
   when substring(@var,5,1) = 'V' then '31'
   when substring(@var,5,1) = 'W' then '32'
   when substring(@var,5,1) = 'X' then '33'
   when substring(@var,5,1) = 'Y' then '34'
   when substring(@var,5,1) = 'Z' then '35'
   ELSE substring(@var,5,1)
  end,
@IBAN6 = case when substring(@var,6,1) = 'A' then '10 '
   when substring(@var,6,1) = 'B' then '11'
   when substring(@var,6,1) = 'C' then '12'
   when substring(@var,6,1) = 'D' then '13'
   when substring(@var,6,1) = 'E' then '14'
   when substring(@var,6,1) = 'F' then '15'
   when substring(@var,6,1) = 'G' then '16'
   when substring(@var,6,1) = 'H' then '17'
   when substring(@var,6,1) = 'I' then '18'
   when substring(@var,6,1) = 'J' then '19'
   when substring(@var,6,1) = 'K' then '20'
   when substring(@var,6,1) = 'L' then '21'
   when substring(@var,6,1) = 'M' then '22'
   when substring(@var,6,1) = 'N' then '23'
   when substring(@var,6,1) = 'O' then '24'
   when substring(@var,6,1) = 'P' then '25'
   when substring(@var,6,1) = 'Q' then '26'
   when substring(@var,6,1) = 'R' then '27'
   when substring(@var,6,1) = 'S' then '28'
   when substring(@var,6,1) = 'T' then '29'
   when substring(@var,6,1) = 'U' then '30'
   when substring(@var,6,1) = 'V' then '31'
   when substring(@var,6,1) = 'W' then '32'
   when substring(@var,6,1) = 'X' then '33'
   when substring(@var,6,1) = 'Y' then '34'
   when substring(@var,6,1) = 'Z' then '35'
   ELSE substring(@var,6,1)
  end,
@IBAN7 = case when substring(@var,7,1) = 'A' then '10 '
   when substring(@var,7,1) = 'B' then '11'
   when substring(@var,7,1) = 'C' then '12'
   when substring(@var,7,1) = 'D' then '13'
   when substring(@var,7,1) = 'E' then '14'
   when substring(@var,7,1) = 'F' then '15'
   when substring(@var,7,1) = 'G' then '16'
   when substring(@var,7,1) = 'H' then '17'
   when substring(@var,7,1) = 'I' then '18'
   when substring(@var,7,1) = 'J' then '19'
   when substring(@var,7,1) = 'K' then '20'
   when substring(@var,7,1) = 'L' then '21'
   when substring(@var,7,1) = 'M' then '22'
   when substring(@var,7,1) = 'N' then '23'
   when substring(@var,7,1) = 'O' then '24'
   when substring(@var,7,1) = 'P' then '25'
   when substring(@var,7,1) = 'Q' then '26'
   when substring(@var,7,1) = 'R' then '27'
   when substring(@var,7,1) = 'S' then '28'
   when substring(@var,7,1) = 'T' then '29'
   when substring(@var,7,1) = 'U' then '30'
   when substring(@var,7,1) = 'V' then '31'
   when substring(@var,7,1) = 'W' then '32'
   when substring(@var,7,1) = 'X' then '33'
   when substring(@var,7,1) = 'Y' then '34'
   when substring(@var,7,1) = 'Z' then '35'
   ELSE substring(@var,7,1)
  end,
@IBAN8 = case when substring(@var,8,1) = 'A' then '10 '
   when substring(@var,8,1) = 'B' then '11'
   when substring(@var,8,1) = 'C' then '12'
   when substring(@var,8,1) = 'D' then '13'
   when substring(@var,8,1) = 'E' then '14'
   when substring(@var,8,1) = 'F' then '15'
   when substring(@var,8,1) = 'G' then '16'
   when substring(@var,8,1) = 'H' then '17'
   when substring(@var,8,1) = 'I' then '18'
   when substring(@var,8,1) = 'J' then '19'
   when substring(@var,8,1) = 'K' then '20'
   when substring(@var,8,1) = 'L' then '21'
   when substring(@var,8,1) = 'M' then '22'
   when substring(@var,8,1) = 'N' then '23'
   when substring(@var,8,1) = 'O' then '24'
   when substring(@var,8,1) = 'P' then '25'
   when substring(@var,8,1) = 'Q' then '26'
   when substring(@var,8,1) = 'R' then '27'
   when substring(@var,8,1) = 'S' then '28'
   when substring(@var,8,1) = 'T' then '29'
   when substring(@var,8,1) = 'U' then '30'
   when substring(@var,8,1) = 'V' then '31'
   when substring(@var,8,1) = 'W' then '32'
   when substring(@var,8,1) = 'X' then '33'
   when substring(@var,8,1) = 'Y' then '34'
   when substring(@var,8,1) = 'Z' then '35'
   ELSE substring(@var,8,1)
  end,
@IBAN9 = case when substring(@var,9,1) = 'A' then '10 '
   when substring(@var,9,1) = 'B' then '11'
   when substring(@var,9,1) = 'C' then '12'
   when substring(@var,9,1) = 'D' then '13'
   when substring(@var,9,1) = 'E' then '14'
   when substring(@var,9,1) = 'F' then '15'
   when substring(@var,9,1) = 'G' then '16'
   when substring(@var,9,1) = 'H' then '17'
   when substring(@var,9,1) = 'I' then '18'
   when substring(@var,9,1) = 'J' then '19'
   when substring(@var,9,1) = 'K' then '20'
   when substring(@var,9,1) = 'L' then '21'
   when substring(@var,9,1) = 'M' then '22'
   when substring(@var,9,1) = 'N' then '23'
   when substring(@var,9,1) = 'O' then '24'
   when substring(@var,9,1) = 'P' then '25'
   when substring(@var,9,1) = 'Q' then '26'
   when substring(@var,9,1) = 'R' then '27'
   when substring(@var,9,1) = 'S' then '28'
   when substring(@var,9,1) = 'T' then '29'
   when substring(@var,9,1) = 'U' then '30'
   when substring(@var,9,1) = 'V' then '31'
   when substring(@var,9,1) = 'W' then '32'
   when substring(@var,9,1) = 'X' then '33'
   when substring(@var,9,1) = 'Y' then '34'
   when substring(@var,9,1) = 'Z' then '35'
   ELSE substring(@var,9,1)
  end,
@IBAN10 = case when substring(@var,10,1) = 'A' then '10 '
   when substring(@var,10,1) = 'B' then '11'
   when substring(@var,10,1) = 'C' then '12'
   when substring(@var,10,1) = 'D' then '13'
   when substring(@var,10,1) = 'E' then '14'
   when substring(@var,10,1) = 'F' then '15'
   when substring(@var,10,1) = 'G' then '16'
   when substring(@var,10,1) = 'H' then '17'
   when substring(@var,10,1) = 'I' then '18'
   when substring(@var,10,1) = 'J' then '19'
   when substring(@var,10,1) = 'K' then '20'
   when substring(@var,10,1) = 'L' then '21'
   when substring(@var,10,1) = 'M' then '22'
   when substring(@var,10,1) = 'N' then '23'
   when substring(@var,10,1) = 'O' then '24'
   when substring(@var,10,1) = 'P' then '25'
   when substring(@var,10,1) = 'Q' then '26'
   when substring(@var,10,1) = 'R' then '27'
   when substring(@var,10,1) = 'S' then '28'
   when substring(@var,10,1) = 'T' then '29'
   when substring(@var,10,1) = 'U' then '30'
   when substring(@var,10,1) = 'V' then '31'
   when substring(@var,10,1) = 'W' then '32'
   when substring(@var,10,1) = 'X' then '33'
   when substring(@var,10,1) = 'Y' then '34'
   when substring(@var,10,1) = 'Z' then '35'
   ELSE substring(@var,10,1)
  end,
@IBAN11 = case when substring(@var,11,1) = 'A' then '10 '
   when substring(@var,11,1) = 'B' then '11'
   when substring(@var,11,1) = 'C' then '12'
   when substring(@var,11,1) = 'D' then '13'
   when substring(@var,11,1) = 'E' then '14'
   when substring(@var,11,1) = 'F' then '15'
   when substring(@var,11,1) = 'G' then '16'
   when substring(@var,11,1) = 'H' then '17'
   when substring(@var,11,1) = 'I' then '18'
   when substring(@var,11,1) = 'J' then '19'
   when substring(@var,11,1) = 'K' then '20'
   when substring(@var,11,1) = 'L' then '21'
   when substring(@var,11,1) = 'M' then '22'
   when substring(@var,11,1) = 'N' then '23'
   when substring(@var,11,1) = 'O' then '24'
   when substring(@var,11,1) = 'P' then '25'
   when substring(@var,11,1) = 'Q' then '26'
   when substring(@var,11,1) = 'R' then '27'
   when substring(@var,11,1) = 'S' then '28'
   when substring(@var,11,1) = 'T' then '29'
   when substring(@var,11,1) = 'U' then '30'
   when substring(@var,11,1) = 'V' then '31'
   when substring(@var,11,1) = 'W' then '32'
   when substring(@var,11,1) = 'X' then '33'
   when substring(@var,11,1) = 'Y' then '34'
   when substring(@var,11,1) = 'Z' then '35'
   ELSE substring(@var,11,1)
  end,
@IBAN12 = case when substring(@var,12,1) = 'A' then '10 '
   when substring(@var,12,1) = 'B' then '11'
   when substring(@var,12,1) = 'C' then '12'
   when substring(@var,12,1) = 'D' then '13'
   when substring(@var,12,1) = 'E' then '14'
   when substring(@var,12,1) = 'F' then '15'
   when substring(@var,12,1) = 'G' then '16'
   when substring(@var,12,1) = 'H' then '17'
   when substring(@var,12,1) = 'I' then '18'
   when substring(@var,12,1) = 'J' then '19'
   when substring(@var,12,1) = 'K' then '20'
   when substring(@var,12,1) = 'L' then '21'
   when substring(@var,12,1) = 'M' then '22'
   when substring(@var,12,1) = 'N' then '23'
   when substring(@var,12,1) = 'O' then '24'
   when substring(@var,12,1) = 'P' then '25'
   when substring(@var,12,1) = 'Q' then '26'
   when substring(@var,12,1) = 'R' then '27'
   when substring(@var,12,1) = 'S' then '28'
   when substring(@var,12,1) = 'T' then '29'
   when substring(@var,12,1) = 'U' then '30'
   when substring(@var,12,1) = 'V' then '31'
   when substring(@var,12,1) = 'W' then '32'
   when substring(@var,12,1) = 'X' then '33'
   when substring(@var,12,1) = 'Y' then '34'
   when substring(@var,12,1) = 'Z' then '35'
   ELSE substring(@var,12,1)
  end,
@IBAN13 = case when substring(@var,13,1) = 'A' then '10 '
   when substring(@var,13,1) = 'B' then '11'
   when substring(@var,13,1) = 'C' then '12'
   when substring(@var,13,1) = 'D' then '13'
   when substring(@var,13,1) = 'E' then '14'
   when substring(@var,13,1) = 'F' then '15'
   when substring(@var,13,1) = 'G' then '16'
   when substring(@var,13,1) = 'H' then '17'
   when substring(@var,13,1) = 'I' then '18'
   when substring(@var,13,1) = 'J' then '19'
   when substring(@var,13,1) = 'K' then '20'
   when substring(@var,13,1) = 'L' then '21'
   when substring(@var,13,1) = 'M' then '22'
   when substring(@var,13,1) = 'N' then '23'
   when substring(@var,13,1) = 'O' then '24'
   when substring(@var,13,1) = 'P' then '25'
   when substring(@var,13,1) = 'Q' then '26'
   when substring(@var,13,1) = 'R' then '27'
   when substring(@var,13,1) = 'S' then '28'
   when substring(@var,13,1) = 'T' then '29'
   when substring(@var,13,1) = 'U' then '30'
   when substring(@var,13,1) = 'V' then '31'
   when substring(@var,13,1) = 'W' then '32'
   when substring(@var,13,1) = 'X' then '33'
   when substring(@var,13,1) = 'Y' then '34'
   when substring(@var,13,1) = 'Z' then '35'
   ELSE substring(@var,13,1)
  end,
@IBAN14 = case when substring(@var,14,1) = 'A' then '10 '
   when substring(@var,14,1) = 'B' then '11'
   when substring(@var,14,1) = 'C' then '12'
   when substring(@var,14,1) = 'D' then '13'
   when substring(@var,14,1) = 'E' then '14'
   when substring(@var,14,1) = 'F' then '15'
   when substring(@var,14,1) = 'G' then '16'
   when substring(@var,14,1) = 'H' then '17'
   when substring(@var,14,1) = 'I' then '18'
   when substring(@var,14,1) = 'J' then '19'
   when substring(@var,14,1) = 'K' then '20'
   when substring(@var,14,1) = 'L' then '21'
   when substring(@var,14,1) = 'M' then '22'
   when substring(@var,14,1) = 'N' then '23'
   when substring(@var,14,1) = 'O' then '24'
   when substring(@var,14,1) = 'P' then '25'
   when substring(@var,14,1) = 'Q' then '26'
   when substring(@var,14,1) = 'R' then '27'
   when substring(@var,14,1) = 'S' then '28'
   when substring(@var,14,1) = 'T' then '29'
   when substring(@var,14,1) = 'U' then '30'
   when substring(@var,14,1) = 'V' then '31'
   when substring(@var,14,1) = 'W' then '32'
   when substring(@var,14,1) = 'X' then '33'
   when substring(@var,14,1) = 'Y' then '34'
   when substring(@var,14,1) = 'Z' then '35'
   ELSE substring(@var,14,1)
  end,
@IBAN15 = case when substring(@var,15,1) = 'A' then '10 '
   when substring(@var,15,1) = 'B' then '11'
   when substring(@var,15,1) = 'C' then '12'
   when substring(@var,15,1) = 'D' then '13'
   when substring(@var,15,1) = 'E' then '14'
   when substring(@var,15,1) = 'F' then '15'
   when substring(@var,15,1) = 'G' then '16'
   when substring(@var,15,1) = 'H' then '17'
   when substring(@var,15,1) = 'I' then '18'
   when substring(@var,15,1) = 'J' then '19'
   when substring(@var,15,1) = 'K' then '20'
   when substring(@var,15,1) = 'L' then '21'
   when substring(@var,15,1) = 'M' then '22'
   when substring(@var,15,1) = 'N' then '23'
   when substring(@var,15,1) = 'O' then '24'
   when substring(@var,15,1) = 'P' then '25'
   when substring(@var,15,1) = 'Q' then '26'
   when substring(@var,15,1) = 'R' then '27'
   when substring(@var,15,1) = 'S' then '28'
   when substring(@var,15,1) = 'T' then '29'
   when substring(@var,15,1) = 'U' then '30'
   when substring(@var,15,1) = 'V' then '31'
   when substring(@var,15,1) = 'W' then '32'
   when substring(@var,15,1) = 'X' then '33'
   when substring(@var,15,1) = 'Y' then '34'
   when substring(@var,15,1) = 'Z' then '35'
   ELSE substring(@var,15,1)
  end,
@IBAN16 = case when substring(@var,16,1) = 'A' then '10 '
   when substring(@var,16,1) = 'B' then '11'
   when substring(@var,16,1) = 'C' then '12'
   when substring(@var,16,1) = 'D' then '13'
   when substring(@var,16,1) = 'E' then '14'
   when substring(@var,16,1) = 'F' then '15'
   when substring(@var,16,1) = 'G' then '16'
   when substring(@var,16,1) = 'H' then '17'
   when substring(@var,16,1) = 'I' then '18'
   when substring(@var,16,1) = 'J' then '19'
   when substring(@var,16,1) = 'K' then '20'
   when substring(@var,16,1) = 'L' then '21'
   when substring(@var,16,1) = 'M' then '22'
   when substring(@var,16,1) = 'N' then '23'
   when substring(@var,16,1) = 'O' then '24'
   when substring(@var,16,1) = 'P' then '25'
   when substring(@var,16,1) = 'Q' then '26'
   when substring(@var,16,1) = 'R' then '27'
   when substring(@var,16,1) = 'S' then '28'
   when substring(@var,16,1) = 'T' then '29'
   when substring(@var,16,1) = 'U' then '30'
   when substring(@var,16,1) = 'V' then '31'
   when substring(@var,16,1) = 'W' then '32'
   when substring(@var,16,1) = 'X' then '33'
   when substring(@var,16,1) = 'Y' then '34'
   when substring(@var,16,1) = 'Z' then '35'
   ELSE substring(@var,16,1)
  end,
@IBAN17 = case when substring(@var,17,1) = 'A' then '10 '
   when substring(@var,17,1) = 'B' then '11'
   when substring(@var,17,1) = 'C' then '12'
   when substring(@var,17,1) = 'D' then '13'
   when substring(@var,17,1) = 'E' then '14'
   when substring(@var,17,1) = 'F' then '15'
   when substring(@var,17,1) = 'G' then '16'
   when substring(@var,17,1) = 'H' then '17'
   when substring(@var,17,1) = 'I' then '18'
   when substring(@var,17,1) = 'J' then '19'
   when substring(@var,17,1) = 'K' then '20'
   when substring(@var,17,1) = 'L' then '21'
   when substring(@var,17,1) = 'M' then '22'
   when substring(@var,17,1) = 'N' then '23'
   when substring(@var,17,1) = 'O' then '24'
   when substring(@var,17,1) = 'P' then '25'
   when substring(@var,17,1) = 'Q' then '26'
   when substring(@var,17,1) = 'R' then '27'
   when substring(@var,17,1) = 'S' then '28'
   when substring(@var,17,1) = 'T' then '29'
   when substring(@var,17,1) = 'U' then '30'
   when substring(@var,17,1) = 'V' then '31'
   when substring(@var,17,1) = 'W' then '32'
   when substring(@var,17,1) = 'X' then '33'
   when substring(@var,17,1) = 'Y' then '34'
   when substring(@var,17,1) = 'Z' then '35'
   ELSE substring(@var,17,1)
  end,
@IBAN18 = case when substring(@var,18,1) = 'A' then '10 '
   when substring(@var,18,1) = 'B' then '11'
   when substring(@var,18,1) = 'C' then '12'
   when substring(@var,18,1) = 'D' then '13'
   when substring(@var,18,1) = 'E' then '14'
   when substring(@var,18,1) = 'F' then '15'
   when substring(@var,18,1) = 'G' then '16'
   when substring(@var,18,1) = 'H' then '17'
   when substring(@var,18,1) = 'I' then '18'
   when substring(@var,18,1) = 'J' then '19'
   when substring(@var,18,1) = 'K' then '20'
   when substring(@var,18,1) = 'L' then '21'
   when substring(@var,18,1) = 'M' then '22'
   when substring(@var,18,1) = 'N' then '23'
   when substring(@var,18,1) = 'O' then '24'
   when substring(@var,18,1) = 'P' then '25'
   when substring(@var,18,1) = 'Q' then '26'
   when substring(@var,18,1) = 'R' then '27'
   when substring(@var,18,1) = 'S' then '28'
   when substring(@var,18,1) = 'T' then '29'
   when substring(@var,18,1) = 'U' then '30'
   when substring(@var,18,1) = 'V' then '31'
   when substring(@var,18,1) = 'W' then '32'
   when substring(@var,18,1) = 'X' then '33'
   when substring(@var,18,1) = 'Y' then '34'
   when substring(@var,18,1) = 'Z' then '35'
   ELSE substring(@var,18,1)
  end,
@IBAN19 = case when substring(@var,19,1) = 'A' then '10 '
   when substring(@var,19,1) = 'B' then '11'
   when substring(@var,19,1) = 'C' then '12'
   when substring(@var,19,1) = 'D' then '13'
   when substring(@var,19,1) = 'E' then '14'
   when substring(@var,19,1) = 'F' then '15'
   when substring(@var,19,1) = 'G' then '16'
   when substring(@var,19,1) = 'H' then '17'
   when substring(@var,19,1) = 'I' then '18'
   when substring(@var,19,1) = 'J' then '19'
   when substring(@var,19,1) = 'K' then '20'
   when substring(@var,19,1) = 'L' then '21'
   when substring(@var,19,1) = 'M' then '22'
   when substring(@var,19,1) = 'N' then '23'
   when substring(@var,19,1) = 'O' then '24'
   when substring(@var,19,1) = 'P' then '25'
   when substring(@var,19,1) = 'Q' then '26'
   when substring(@var,19,1) = 'R' then '27'
   when substring(@var,19,1) = 'S' then '28'
   when substring(@var,19,1) = 'T' then '29'
   when substring(@var,19,1) = 'U' then '30'
   when substring(@var,19,1) = 'V' then '31'
   when substring(@var,19,1) = 'W' then '32'
   when substring(@var,19,1) = 'X' then '33'
   when substring(@var,19,1) = 'Y' then '34'
   when substring(@var,19,1) = 'Z' then '35'
   ELSE substring(@var,19,1)
  end,
@IBAN20 = case when substring(@var,20,1) = 'A' then '10 '
   when substring(@var,20,1) = 'B' then '11'
   when substring(@var,20,1) = 'C' then '12'
   when substring(@var,20,1) = 'D' then '13'
   when substring(@var,20,1) = 'E' then '14'
   when substring(@var,20,1) = 'F' then '15'
   when substring(@var,20,1) = 'G' then '16'
   when substring(@var,20,1) = 'H' then '17'
   when substring(@var,20,1) = 'I' then '18'
   when substring(@var,20,1) = 'J' then '19'
   when substring(@var,20,1) = 'K' then '20'
   when substring(@var,20,1) = 'L' then '21'
   when substring(@var,20,1) = 'M' then '22'
   when substring(@var,20,1) = 'N' then '23'
   when substring(@var,20,1) = 'O' then '24'
   when substring(@var,20,1) = 'P' then '25'
   when substring(@var,20,1) = 'Q' then '26'
   when substring(@var,20,1) = 'R' then '27'
   when substring(@var,20,1) = 'S' then '28'
   when substring(@var,20,1) = 'T' then '29'
   when substring(@var,20,1) = 'U' then '30'
   when substring(@var,20,1) = 'V' then '31'
   when substring(@var,20,1) = 'W' then '32'
   when substring(@var,20,1) = 'X' then '33'
   when substring(@var,20,1) = 'Y' then '34'
   when substring(@var,20,1) = 'Z' then '35'
   ELSE substring(@var,20,1)
  end,
@IBAN21 = case when substring(@var,21,1) = 'A' then '10 '
   when substring(@var,21,1) = 'B' then '11'
   when substring(@var,21,1) = 'C' then '12'
   when substring(@var,21,1) = 'D' then '13'
   when substring(@var,21,1) = 'E' then '14'
   when substring(@var,21,1) = 'F' then '15'
   when substring(@var,21,1) = 'G' then '16'
   when substring(@var,21,1) = 'H' then '17'
   when substring(@var,21,1) = 'I' then '18'
   when substring(@var,21,1) = 'J' then '19'
   when substring(@var,21,1) = 'K' then '20'
   when substring(@var,21,1) = 'L' then '21'
   when substring(@var,21,1) = 'M' then '22'
   when substring(@var,21,1) = 'N' then '23'
   when substring(@var,21,1) = 'O' then '24'
   when substring(@var,21,1) = 'P' then '25'
   when substring(@var,21,1) = 'Q' then '26'
   when substring(@var,21,1) = 'R' then '27'
   when substring(@var,21,1) = 'S' then '28'
   when substring(@var,21,1) = 'T' then '29'
   when substring(@var,21,1) = 'U' then '30'
   when substring(@var,21,1) = 'V' then '31'
   when substring(@var,21,1) = 'W' then '32'
   when substring(@var,21,1) = 'X' then '33'
   when substring(@var,21,1) = 'Y' then '34'
   when substring(@var,21,1) = 'Z' then '35'
   ELSE substring(@var,21,1)
  end,
@IBAN22 = case when substring(@var,22,1) = 'A' then '10 '
   when substring(@var,22,1) = 'B' then '11'
   when substring(@var,22,1) = 'C' then '12'
   when substring(@var,22,1) = 'D' then '13'
   when substring(@var,22,1) = 'E' then '14'
   when substring(@var,22,1) = 'F' then '15'
   when substring(@var,22,1) = 'G' then '16'
   when substring(@var,22,1) = 'H' then '17'
   when substring(@var,22,1) = 'I' then '18'
   when substring(@var,22,1) = 'J' then '19'
   when substring(@var,22,1) = 'K' then '20'
   when substring(@var,22,1) = 'L' then '21'
   when substring(@var,22,1) = 'M' then '22'
   when substring(@var,22,1) = 'N' then '23'
   when substring(@var,22,1) = 'O' then '24'
   when substring(@var,22,1) = 'P' then '25'
   when substring(@var,22,1) = 'Q' then '26'
   when substring(@var,22,1) = 'R' then '27'
   when substring(@var,22,1) = 'S' then '28'
   when substring(@var,22,1) = 'T' then '29'
   when substring(@var,22,1) = 'U' then '30'
   when substring(@var,22,1) = 'V' then '31'
   when substring(@var,22,1) = 'W' then '32'
   when substring(@var,22,1) = 'X' then '33'
   when substring(@var,22,1) = 'Y' then '34'
   when substring(@var,22,1) = 'Z' then '35'
   ELSE substring(@var,22,1)
  end,
@IBAN23 = case when substring(@var,23,1) = 'A' then '10 '
   when substring(@var,23,1) = 'B' then '11'
   when substring(@var,23,1) = 'C' then '12'
   when substring(@var,23,1) = 'D' then '13'
   when substring(@var,23,1) = 'E' then '14'
   when substring(@var,23,1) = 'F' then '15'
   when substring(@var,23,1) = 'G' then '16'
   when substring(@var,23,1) = 'H' then '17'
   when substring(@var,23,1) = 'I' then '18'
   when substring(@var,23,1) = 'J' then '19'
   when substring(@var,23,1) = 'K' then '20'
   when substring(@var,23,1) = 'L' then '21'
   when substring(@var,23,1) = 'M' then '22'
   when substring(@var,23,1) = 'N' then '23'
   when substring(@var,23,1) = 'O' then '24'
   when substring(@var,23,1) = 'P' then '25'
   when substring(@var,23,1) = 'Q' then '26'
   when substring(@var,23,1) = 'R' then '27'
   when substring(@var,23,1) = 'S' then '28'
   when substring(@var,23,1) = 'T' then '29'
   when substring(@var,23,1) = 'U' then '30'
   when substring(@var,23,1) = 'V' then '31'
   when substring(@var,23,1) = 'W' then '32'
   when substring(@var,23,1) = 'X' then '33'
   when substring(@var,23,1) = 'Y' then '34'
   when substring(@var,23,1) = 'Z' then '35'
   ELSE substring(@var,23,1)
  end,
@IBAN24 = case when substring(@var,24,1) = 'A' then '10 '
   when substring(@var,24,1) = 'B' then '11'
   when substring(@var,24,1) = 'C' then '12'
   when substring(@var,24,1) = 'D' then '13'
   when substring(@var,24,1) = 'E' then '14'
   when substring(@var,24,1) = 'F' then '15'
   when substring(@var,24,1) = 'G' then '16'
   when substring(@var,24,1) = 'H' then '17'
   when substring(@var,24,1) = 'I' then '18'
   when substring(@var,24,1) = 'J' then '19'
   when substring(@var,24,1) = 'K' then '20'
   when substring(@var,24,1) = 'L' then '21'
   when substring(@var,24,1) = 'M' then '22'
   when substring(@var,24,1) = 'N' then '23'
   when substring(@var,24,1) = 'O' then '24'
   when substring(@var,24,1) = 'P' then '25'
   when substring(@var,24,1) = 'Q' then '26'
   when substring(@var,24,1) = 'R' then '27'
   when substring(@var,24,1) = 'S' then '28'
   when substring(@var,24,1) = 'T' then '29'
   when substring(@var,24,1) = 'U' then '30'
   when substring(@var,24,1) = 'V' then '31'
   when substring(@var,24,1) = 'W' then '32'
   when substring(@var,24,1) = 'X' then '33'
   when substring(@var,24,1) = 'Y' then '34'
   when substring(@var,24,1) = 'Z' then '35'
   ELSE substring(@var,24,1)
  end

select @IBAN1,@IBAN2,@IBAN3,@IBAN4,@IBAN5,@IBAN6,@IBAN7,@IBAN8,@IBAN9,@IBAN10,@IBAN11,@IBAN12,@IBAN13,@IBAN14,@IBAN15,@IBAN16,@IBAN17,@IBAN18,@IBAN19,@IBAN20,@IBAN21,@IBAN22,@IBAN23,@IBAN24

declare @FinalIBAN varchar(200)
select @FinalIBAN = @IBAN1+@IBAN2+@IBAN3+@IBAN4+@IBAN5+@IBAN6+@IBAN7+@IBAN8+@IBAN9+@IBAN10+@IBAN11+@IBAN12+@IBAN13+@IBAN14+@IBAN15+@IBAN16+@IBAN17+@IBAN18+@IBAN19+@IBAN20+@IBAN21+@IBAN22+@IBAN23+@IBAN24

declare @FinalNumberIBAN float
select @FinalIBAN
select @FinalNumberIBAN = convert(float,@FinalIBAN)
select @FinalNumberIBAN

declare @rest float
select @rest = @FinalNumberIBAN % 97.0
select @rest

multumesc.

alex.

Re: functie sql care verifica IBAN-ul

Alex — Fri, 25 Jul 2008 13:12:58 GMT

am o problema,

dupa ce am convertit IBAN-ul adica l-am adus de forma 1010101011131007593840000272449 (cum spune in linkul de mai sus)

declare @var int
set @var = 1010101011131007593840000272449
select @var

primesc eroarea:

Arithmetic overflow error converting expression to data type int.

de ce timp trebuie sa fie acest numar ca sa-l pot imparti la 97.

multumesc.

alex.

Re: functie sql care verifica IBAN-ul

Alex — Fri, 25 Jul 2008 12:39:15 GMT

eu m-am uitat aici

http://www.validari.ro/iban.htm

si mi se pare ca se poate face, asta e algoritmul.

deci, daca cineva poseda deja functia asta va rog sa mi-o dati si mie

multumesc.

alex.

Re: functie sql care verifica IBAN-ul

ignatandrei — Fri, 25 Jul 2008 11:53:06 GMT

Daca te uiti in Wikipedia, IBAN-urile pot avea diverse forme in functie de tara
http://en.wikipedia.org/wiki/IBAN
Are si checksum ...