| |
To calculate bank routing check digit (digit #9 is used to validate 8-digit bank routing (ABA) number) -
ABA Routing Number - A nine digit number (eight digits plus a check digit) which identifies a specific financial
institution. Routing numbers are administered by the Routing Number Administrative Board under the sponsorship of the American Bankers Association and officially maintained and published by Thomson Financial Publishing.
See MAP Check Processing
Don't get caught with bogus or erroneous routing numbers, that can cost you a heap of problems, and money,
such as Bank return charges, delay of payment, recovery, and other associated costs.
Use MAP Check Verification and
MAP Check Guarantee programs.
In general terms, A check digit is a form of redundancy check used for error detection, the decimal equivalent of a binary checksum.
It consists of a single digit computed from the other digits in the message.
This article shows procedures and algorithms for Verifying the accuracy and validity of ABA (routing/transit) numbers.
A check digit is a digit added to a number at the end validates the authenticity of the number.
A simple algorithm is applied to the other digits of the number which yields the check digit.
By running the algorithm, and comparing the check digit you get from the algorithm with the check digit encoded with the routing number,
you can verify that you have correctly read all of the digits and that they make a valid combination.
Checks typically bear a nine-digit ABA routing number printed in the bottom left-hand corner.
In general, the first four digits are a Federal Reserve routing symbol, identifying which of the twelve Federal Reserve districts the check was printed in (and a city within that district).
The next four digits are an ABA institution identifier which designates the bank on which the check is drawn, and the last number is a check digit.
CREATE FUNCTION create_check_digit (@routing_number char(8) ) returns int
as
begin
declare @digit int
,@sum_digit int
declare @check_digit int
set @check_digit=0
--1
set @digit =convert(int, substring(@routing_number, 1, 1))
set @sum_digit = @digit*3
--2
set @digit =convert(int, substring(@routing_number, 2, 1))
set @sum_digit = @sum_digit + @digit*7
--3
set @digit =convert(int, substring(@routing_number, 3, 1))
set @sum_digit = @sum_digit + @digit*1
--4
set @digit =convert(int, substring(@routing_number, 4, 1))
set @sum_digit = @sum_digit + @digit*3
--5
set @digit =convert(int, substring(@routing_number, 5, 1))
set @sum_digit = @sum_digit + @digit*7
--6
set @digit =convert(int, substring(@routing_number, 6, 1))
set @sum_digit = @sum_digit + @digit*1
--7
set @digit =convert(int, substring(@routing_number, 7, 1))
set @sum_digit = @sum_digit + @digit*3
--8
set @digit =convert(int, substring(@routing_number, 8, 1))
set @sum_digit = @sum_digit + @digit*7
if (@sum_digit % 10) > 0
set @check_digit = 10 - (@sum_digit % 10)
return @check_digit
end
To verify bank routing number by calculating correct check digit (digit #9 is used to validate 8-digit bank routing (ABA) number)
and compare with digit #9 in given 9-digit routing number:
CREATE FUNCTION check_digit (@routing_number char(9) ) returns int
as
begin
declare @digit int
,@sum_digit int
declare @check_digit int
set @check_digit=0
--1
if isnumeric(substring(@routing_number, 1, 1))=0 return -991
set @digit =convert(int, substring(@routing_number, 1, 1))
set @sum_digit = @digit*3
--2
if isnumeric(substring(@routing_number, 2, 1))=0 return -992
set @digit =convert(int, substring(@routing_number, 2, 1))
set @sum_digit = @sum_digit + @digit*7
--3
if isnumeric(substring(@routing_number, 3, 1))=0 return -993
set @digit =convert(int, substring(@routing_number, 3, 1))
set @sum_digit = @sum_digit + @digit*1
--4
if isnumeric(substring(@routing_number, 4, 1))=0 return -994
set @digit =convert(int, substring(@routing_number, 4, 1))
set @sum_digit = @sum_digit + @digit*3
--5
if isnumeric(substring(@routing_number, 5, 1))=0 return -995
set @digit =convert(int, substring(@routing_number, 5, 1))
set @sum_digit = @sum_digit + @digit*7
--6
if isnumeric(substring(@routing_number, 6, 1))=0 return -996
set @digit =convert(int, substring(@routing_number, 6, 1))
set @sum_digit = @sum_digit + @digit*1
--7
if isnumeric(substring(@routing_number, 7, 1))=0 return -997
set @digit =convert(int, substring(@routing_number, 7, 1))
set @sum_digit = @sum_digit + @digit*3
--8
if isnumeric(substring(@routing_number, 8, 1))=0 return -998
set @digit =convert(int, substring(@routing_number, 8, 1))
set @sum_digit = @sum_digit + @digit*7
-- last one
if isnumeric(substring(@routing_number, 9, 1))=0 return -999
set @digit =convert(int, substring(@routing_number, 9, 1))
--select substring(@routing_number, 9, 1), len(@routing_number)
if (@sum_digit % 10) > 0
set @check_digit = 10 - (@sum_digit % 10)
if @digit<> @check_digit
begin
--print 'Invalid routing =' + @routing_number
--select @digit, @check_digit
select @check_digit=-99
end
return @check_digit
end
Side Note
SQL Substring() function:
Returns part of a character, binary, text, or image expression.
Where: SUBSTRING ( <expression>, <start>, <length>):
- Expression - character string, binary string, text, image, a column, or an expression that includes a column.
Do not use expressions that include aggregate functions.
- Start - integer that specifies where the substring begins.
- Length - integer that specifies the length of the substring (the number of characters or bytes to return).
The SUBSTRING function returns a portion of either a character or binary string, or a text string, and takes three parameters:
- A character or binary string, a column name, or a string-valued expression that includes a column name.
- The position at which the substring should begin.
- The length (in number of characters, or in number of bytes for binary) of the string to be returned.
SQL IsNumeric() function:
Determines whether an expression is a valid numeric type
and returns 1 when the input expression evaluates to a valid integer, floating point number, money or decimal type; otherwise it returns 0.
Where ISNUMERIC ( <expression>)
Create Function:
Creates User-Defined Function (UDF) - subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse.
User-defined functions are created using the CREATE FUNCTION statement, modified using the ALTER FUNCTION statement, and removed using the DROP FUNCTION statement. Each fully qualified user-defined function name (database_name.owner_name.function_name) must be unique.
Side Note
Routing Number and Check Digit:
in 1910-1911, American Bankers Association (ABA) developed and implemented
numerical identification of financial institutions - banks. The Routing Number Administrative Board of the American Bankers Association governs the assignment, use and retirement of routing numbers.
Nowdays, checks are encoded with three sets of machine-readable numbers, all appearing across the bottom of the document: the routing number; the customer's account number and optional internal bank transaction codes (in what is called the "on-us" field); and the amount of the check. An additional number may be included on commercial or business checks containing the check number or other indicative data (in what is called the "auxiliary on-us field").
Nine digit Bank Routing Number contains three stes of information: Federal Reserve Routing Symbol, ABA Institution Identifier
and check digit.
Where:
- Federal Reserve Routing Symbol - identify the origin and routing pattern of an item by showing the Federal Reserve district, office and clearing arrangement used by the originating or drawee bank. Routing symbols were only assigned to eligible institutions whose items were collectible through Federal Reserve offices.
The routing symbol is composed of four digits, each with a special meaning.
- ABA Institution Identifier - each eligible financial institution receives a unique identifier comprising one to four digits.
- Check Digit - result of mathematical calculations that are used to verify the accuracy of the routing number. It is critical to the integrity of electronic and telecommunications transactions, and must also appear in print with the rest of the digits.
To calculate the check digit, a routine called "Modules 10, Straight Summation" is used.
|
Questions? Suggestions? Concerns? - email me to [email protected]
Greg Dubinovsky � 2006