- Re: functie sql care verifica IBAN-ul

Welcome to Sign in | Help

Home

Blogs

External Feeds

» Romanian Section » Intrebari » T-SQL » Re: functie sql care verifica IBAN-ul

Re: functie sql care verifica IBAN-ul

07-26-2008, 3:50 AM

Alex
Joined on 10-25-2006
Bucuresti
sysadmin

Re: functie sql care verifica IBAN-ul

Reply Quote

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

Report abuse

View Complete Thread

Powered by Community Server (Commercial Edition), by Telligent Systems