Welcome to Sign in | Help
Search
Home Blogs External Feeds Forums Photos Downloads About Us
» Romanian Section » Intrebari » T-SQL » Re: functie sql care verifica IBAN-ul

Re: functie sql care verifica IBAN-ul

  •  07-25-2008, 4:36 PM

    Re: functie sql care verifica IBAN-ul

    Reply Quote

    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.
View Complete Thread
Powered by Community Server (Commercial Edition), by Telligent Systems