Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Monday, July 8, 2019 8:06 PM
Hi,
For my work, I'm looking for a T-SQL code which can check IBAN account numbers (a table filled with customers and the IBAN account numbers). Spent some real time on the net to find something, but all I got was a MYSQL code (https://github.com/amnesty/dataquality/blob/master/src/mysql/iban.sql.)) which could be working, but I can't check this one in MS. Maybe someone has a working code in T-SQL for this (like the backbone of this site, which would not share it's code obviously... https://www.ibancalculator.com/)?
Tuesday, July 9, 2019 6:39 AM ✅Answered | 1 vote
Hi Nemesis1234,
Thank you for your issue. I look into the web you provided and find one code which is T-SQL and can be used in SSMS in MS. I tested and it works. You can take that as a reference.
https://github.com/amnesty/dataquality/find/master
https://github.com/amnesty/dataquality/blob/master/src/sqlserver/iban.sql
IF object_id('getIBANControlDigits', 'FN') IS NOT NULL
DROP FUNCTION getIBANControlDigits
GO
CREATE FUNCTION getIBANControlDigits( @accNumber VARCHAR(64) )
RETURNS VARCHAR(64)
BEGIN
/*
This function expects the entire account number in the electronic
format (without spaces), as described in the ISO 13616-Compliant
IBAN Formats document.
You can replace control @digits with zeros when calling the function.
This function requires:
- replaceLetterWithDigits
- accountLegthPerCountry (table)
Usage:
SELECT dbo.getIBANControlDigits( 'GB00WEST12345698765432' )
Returns:
82
*/
DECLARE @countryCode VARCHAR(2)
DECLARE @accountLength INT
DECLARE @accRearranged VARCHAR(64)
DECLARE @accWithoutLetters VARCHAR(64)
DECLARE @accMod97 INT
DECLARE @digits VARCHAR(2)
SET @countryCode = LEFT( @accNumber, 2 )
SET @accountLength =
( SELECT TOP 1 lc.accountLength FROM accountLegthPerCountry AS lc
WHERE lc.countryCode = @countryCode)
IF ( LEN( @accNumber ) = @accountLength )
BEGIN
/* Replace the two check @digits by 00 (e.g., GB00 for the UK) and
Move the four initial characters to the end of the string. */
SET @accRearranged = RIGHT( @accNumber, LEN( @accNumber ) - 4 ) + LEFT( @accNumber, 2 ) + '00'
/* Replace the letters in the string with @digits, expanding the string as necessary,
such that A or a = 10, B or b = 11, and Z or z = 35.
Each alphabetic character is therefore replaced by 2 @digits. */
SET @accWithoutLetters = dbo.replaceLetterWithDigits( @accRearranged )
/* Convert the string to an integer (i.e., ignore leading zeroes) and
Calculate mod-97 of the new number, which results in the remainder. */
SET @accMod97 = CAST( @accWithoutLetters AS DECIMAL(64) ) % 97
/* Subtract the remainder from 98, and use the result for the two check @digits. */
SET @digits = 98 - @accMod97
/* If the result is a single digit number, pad it with a leading 0 to make a two-digit number. */
SET @digits = RIGHT( '00' + @digits, 2)
END
ELSE
BEGIN
SET @digits = ''
END
RETURN @digits
END
GO
EXECUTE sys.sp_addextendedproperty
@name = 'MS_Description',
@value = 'Obtains the correct IBAN control @digits for a given account number',
@level0type = 'SCHEMA',
@level0name = 'dbo',
@level1type = 'function',
@level1name = 'getIBANControlDigits'
GO
IF object_id('getGlobalIdentifier', 'FN') IS NOT NULL
DROP FUNCTION getGlobalIdentifier
GO
CREATE FUNCTION getGlobalIdentifier(
@localId VARCHAR(64), @countryCode VARCHAR(2), @suffix VARCHAR(3) )
RETURNS VARCHAR(64)
BEGIN
/*
The identification numbers used in Sepa are calculated from the local
identification numbers. For instance, if your Spanish (local)
identification number is G28667152, your global identification
number must be ES03000G28667152.
This function requires:
- replaceLetterWithDigits
- replaceCharactersNotInPattern
Usage:
SELECT dbo.getGlobalIdentifier( 'G28667152', 'ES', '' )
Returns:
ES03000G28667152
*/
DECLARE @alphaNumerical VARCHAR(64)
DECLARE @withCountry VARCHAR(64)
DECLARE @withoutLetters VARCHAR(64)
DECLARE @mod97 INT
DECLARE @digits VARCHAR(2)
DECLARE @globalId VARCHAR(64)
/* Removes non alpha-numerical characters */
SET @alphaNumerical = dbo.replaceCharactersNotInPattern( @localId,
'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', '' )
/* Adds country plus '00' at the end */
SET @withCountry = @alphaNumerical + @countryCode + '00'
/* Replace the letters in the string with digits, expanding the string as necessary,
such that A or a = 10, B or b = 11, and Z or z = 35.
Each alphabetic character is therefore replaced by 2 digits. */
SET @withoutLetters = dbo.replaceLetterWithDigits( @withCountry )
/* Convert the string to an integer (i.e., ignore leading zeroes) and
Calculate mod-97 of the new number, which results in the remainder. */
SET @mod97 = CAST( @withoutLetters AS DECIMAL(64) ) % 97;
/* Subtract the remainder from 98, and use the result for the two check digits. */
SET @digits = 98 - @mod97
/* If the result is a single digit number, pad it with a leading 0 to make a two-digit number. */
SET @digits = RIGHT( '00' + @digits, 2);
/* Suffix must be a number from 000 to 999 */
SET @suffix = dbo.replaceCharactersNotInPattern( @suffix, '0123456789', '0' );
SET @suffix = RIGHT( '000' + @suffix, 3 );
RETURN @countryCode + @digits + @suffix + @localId
END
GO
EXECUTE sys.sp_addextendedproperty
@name = 'MS_Description',
@value = 'Obtains the global identifier for a given local identifier',
@level0type = 'SCHEMA',
@level0name = 'dbo',
@level1type = 'function',
@level1name = 'getGlobalIdentifier'
GO
IF object_id('replaceCharactersNotInPattern', 'FN') IS NOT NULL
DROP FUNCTION replaceCharactersNotInPattern
GO
CREATE FUNCTION replaceCharactersNotInPattern(
@givenString VARCHAR(256), @pattern VARCHAR(64), @replaceWith VARCHAR(1) )
RETURNS VARCHAR(256)
BEGIN
/*
This function replaces unwanted characters from a string with a given
character.
If a string 'ABCDEF%' and a pattern 'ABDF' are given, it returns
a new string where:
- the characters in the string that respect the patter remain unchanged
- other characters are replaced with the given substitution character
This function is used by:
- getGlobalIdentifier
Usage:
SELECT dbo.replaceCharactersNotInPattern(
'ABC123-?:', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789', '0' )
Returns:
'ABC123000'
*/
DECLARE @verifyLetter VARCHAR(1)
DECLARE @i INT
SET @i = 0
WHILE ( @i <= LEN( @givenString ) )
BEGIN
SET @verifyLetter = SUBSTRING( @givenString, @i, 1 )
IF CHARINDEX( @verifyLetter, @pattern ) = 0
BEGIN
SET @givenString = REPLACE( @givenString, @verifyLetter, @replaceWith )
END
SET @i = @i + 1
END
RETURN @givenString
END
GO
EXECUTE sys.sp_addextendedproperty
@name = 'MS_Description',
@value = 'Replace unwanted characters in a given string with a character',
@level0type = 'SCHEMA',
@level0name = 'dbo',
@level1type = 'function',
@level1name = 'replaceCharactersNotInPattern'
GO
IF object_id('replaceLetterWithDigits', 'FN') IS NOT NULL
DROP FUNCTION replaceLetterWithDigits
GO
CREATE FUNCTION replaceLetterWithDigits( @accNumber VARCHAR(64) )
RETURNS VARCHAR(64)
BEGIN
/*
This functions changes letters in a given string
with its correspondant numbers, as described in
ECBS EBS204 V3.2 [August 2003] document:
A=1, B=2, ..., Y=34, Z=35
Usage:
SELECT dbo.replaceLetterWithDigits( '510007547061BE00' )
Returns:
510007547061111400
*/
DECLARE @letters VARCHAR(64)
DECLARE @findLetter VARCHAR(1)
DECLARE @replaceWith INT
DECLARE @i INT
SET @i = 0
SET @letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
WHILE ( @i <= LEN( @letters ) )
BEGIN
SET @findLetter = SUBSTRING( @letters, @i, 1 )
SET @replaceWith = CHARINDEX( @findLetter, @letters ) + 9
SET @accNumber = REPLACE( @accNumber, @findLetter, @replaceWith )
SET @i = @i + 1
END
RETURN @accNumber
END
GO
EXECUTE sys.sp_addextendedproperty
@name = 'MS_Description',
@value = 'Replaces letters with numbers (taking A=1 and Z=35)',
@level0type = 'SCHEMA',
@level0name = 'dbo',
@level1type = 'function',
@level1name = 'replaceLetterWithDigits'
GO
IF object_id('accountLegthPerCountry', 'U') IS NOT NULL
DROP TABLE accountLegthPerCountry
GO
CREATE TABLE accountLegthPerCountry (
countryCode VARCHAR(2),
accountLength INT,
PRIMARY KEY( countryCode )
)
GO
EXECUTE sys.sp_addextendedproperty
@name = 'MS_Description',
@value = 'IBAN length -in characters- per country',
@level0type = 'SCHEMA',
@level0name = 'dbo',
@level1type = 'table',
@level1name = 'accountLegthPerCountry'
GO
/* Information Source: IBAN Registry about all ISO 13616-compliant
national IBAN formats (Release 45 – April 2013).
https://www.swift.com/sites/default/files/resources/iban_registry.pdf */
INSERT INTO accountLegthPerCountry (countryCode, accountLength)
SELECT 'AL', '28' UNION ALL SELECT 'AD', '24' UNION ALL SELECT 'AT', '20' UNION ALL SELECT 'AZ', '28' UNION ALL
SELECT 'BH', '22' UNION ALL SELECT 'BE', '16' UNION ALL SELECT 'BA', '20' UNION ALL SELECT 'BR', '29' UNION ALL
SELECT 'BG', '22' UNION ALL SELECT 'CR', '21' UNION ALL SELECT 'HR', '21' UNION ALL SELECT 'CY', '28' UNION ALL
SELECT 'CZ', '24' UNION ALL SELECT 'DK', '18' UNION ALL SELECT 'DO', '28' UNION ALL SELECT 'EE', '20' UNION ALL
SELECT 'FO', '18' UNION ALL SELECT 'FI', '18' UNION ALL SELECT 'FR', '27' UNION ALL SELECT 'GE', '22' UNION ALL
SELECT 'DE', '22' UNION ALL SELECT 'GI', '23' UNION ALL SELECT 'GR', '27' UNION ALL SELECT 'GL', '18' UNION ALL
SELECT 'GT', '28' UNION ALL SELECT 'HU', '28' UNION ALL SELECT 'IS', '26' UNION ALL SELECT 'IE', '22' UNION ALL
SELECT 'IL', '23' UNION ALL SELECT 'IT', '27' UNION ALL SELECT 'KZ', '20' UNION ALL SELECT 'KW', '30' UNION ALL
SELECT 'LV', '21' UNION ALL SELECT 'LB', '28' UNION ALL SELECT 'LI', '21' UNION ALL SELECT 'LT', '20' UNION ALL
SELECT 'LU', '20' UNION ALL SELECT 'MK', '19' UNION ALL SELECT 'MT', '31' UNION ALL SELECT 'MR', '27' UNION ALL
SELECT 'MU', '30' UNION ALL SELECT 'MC', '27' UNION ALL SELECT 'MD', '24' UNION ALL SELECT 'ME', '22' UNION ALL
SELECT 'NL', '18' UNION ALL SELECT 'NO', '15' UNION ALL SELECT 'PK', '24' UNION ALL SELECT 'PS', '29' UNION ALL
SELECT 'PL', '28' UNION ALL SELECT 'PT', '25' UNION ALL SELECT 'RO', '24' UNION ALL SELECT 'SM', '27' UNION ALL
SELECT 'SA', '24' UNION ALL SELECT 'RS', '22' UNION ALL SELECT 'SK', '24' UNION ALL SELECT 'SI', '19' UNION ALL
SELECT 'ES', '24' UNION ALL SELECT 'SE', '24' UNION ALL SELECT 'CH', '21' UNION ALL SELECT 'TN', '24' UNION ALL
SELECT 'TR', '26' UNION ALL SELECT 'AE', '23' UNION ALL SELECT 'GB', '22' UNION ALL SELECT 'VG', '24' UNION ALL
SELECT 'AO', '25' UNION ALL SELECT 'BJ', '28' UNION ALL SELECT 'BF', '27' UNION ALL SELECT 'BI', '16' UNION ALL
SELECT 'CM', '27' UNION ALL SELECT 'CV', '25' UNION ALL SELECT 'IR', '26' UNION ALL SELECT 'CI', '28' UNION ALL
SELECT 'MG', '27' UNION ALL SELECT 'ML', '28' UNION ALL SELECT 'MZ', '25' UNION ALL SELECT 'SN', '28'
Regards,
Sabrina
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
Tuesday, July 9, 2019 2:31 PM ✅Answered | 1 vote
SELECT dbo.getIBANControlDigits( 'NL37SNSB0921261357' ) returns 37
The two digits after the country code is the check Digit and the function returns the calculated check Digit; if both are equal then the IBAN is correct.
Olaf Helper
Wednesday, July 10, 2019 12:03 AM ✅Answered | 1 vote
Create Table #Test(ID int, Name varchar(20), IBAN varchar(64));
Insert #Test(ID, Name, IBAN) Values
(1, 'First Valid', 'NL37SNSB0921261357'),
(2, 'First InValid', 'NL43SNSB0921261357'),
(3, 'Second InValid', 'NL45SNSB0921'),
(4, 'Second Valid', 'NL97INGB0665381026'),
(5, 'Third Invalid', 'NL77RABO0323232321')
Select t.ID, t.Name, t.IBAN, SubString(t.IBAN, 3, 2) As ActualCheckDigits, i.ValidCheckDigits
From #Test t
Cross Apply (Select dbo.getIBANControlDigits( t.IBAN) As ValidCheckDigits) i
Where SubString(t.IBAN, 3, 2) <> i.ValidCheckDigits
Tom
Monday, July 8, 2019 10:13 PM
That link returns a 404 not found error. But the short answer is that such code apparently does not exist (freely) if google cannot find it. You can write it yourself, hire someone to do it, or integrate such logic into your system using APIs provided by others.
Tuesday, July 9, 2019 2:49 AM
It depends on what you are looking for. If all you want is some elementary checks like the length is correct and the character set is correct and the MOD-97 check digits are correct, then you could look at the following site which has SQL Server code: https://analisisyprogramacionoop.blogspot.com/2014/10/iban-y-bic-validacion-con-sql-server-y.html
That site is in Spanish, but even if you don't read Spanish, the SQL Server code should be understandable. Or you could try to translate the webpage using Google Translate.
But if you also need all the country specific verification (like Austria IBAN codes must be 20 characters beginning with AT and Belgium IBAN codes are 16 characters beginning with BE and have national check digits), the code in the above site won't do that.
(Note that I just found this site, but have never used it, so I cannot verify the SQL Server code is correct.)
Tom
Tuesday, July 9, 2019 2:11 PM
Thank you for your reply!
Yes, I tried this one before. However, using (Dutch) IBANs, I get in return different values... For example:
SELECT dbo.getIBANControlDigits( 'NL37SNSB0921261357' ) returns 37
SELECT dbo.getIBANControlDigits( 'NL97INGB0665381026' ) returns 97
SELECT dbo.getIBANControlDigits( 'NL19ABNA0426126068' ) returns 19
As far as I understand, it should always return 82 IF the IBAN is correct (above IBANS are correct).
I assume I don't need to alter the code, since the country code is 'grabbed' automatically?
Tuesday, July 9, 2019 2:38 PM
SELECT dbo.getIBANControlDigits( 'NL37SNSB0921261357' ) returns 37
The two digits after the country code is the check Digit and the function returns the calculated check Digit; if both are equal then the IBAN is correct.
Olaf Helper
Ahh... Since several sites mentioned the example '82' I didn't count on that one... :-) Thanks a lot!!
Tuesday, July 9, 2019 2:56 PM
Now the only question is... I do get one number as output which corresponds with the 2 digits. But how do I turn this one into a check code for a whole table filled with ID's, names and IBAN numbers?
Best outcome for me should be a list of the faulty IBAN numbers like:
ID1, Name1, NL54INGB0006354174
ID2, Name2, NL77RABO032323232
Etc.
But how is that possible with this one?
Wednesday, July 10, 2019 1:10 PM
Create Table #Test(ID int, Name varchar(20), IBAN varchar(64)); Insert #Test(ID, Name, IBAN) Values (1, 'First Valid', 'NL37SNSB0921261357'), (2, 'First InValid', 'NL43SNSB0921261357'), (3, 'Second InValid', 'NL45SNSB0921'), (4, 'Second Valid', 'NL97INGB0665381026'), (5, 'Third Invalid', 'NL77RABO0323232321') Select t.ID, t.Name, t.IBAN, SubString(t.IBAN, 3, 2) As ActualCheckDigits, i.ValidCheckDigits From #Test t Cross Apply (Select dbo.getIBANControlDigits( t.IBAN) As ValidCheckDigits) i Where SubString(t.IBAN, 3, 2) <> i.ValidCheckDigitsTom
Works like a charm, thanks!!!