Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool     Our sponsor - Loose the Razor - Use the Laser at Clarity Medispa Laser Hair Removal

Siccolo Development Articles - Calculate Bank Routing Check Digit, Check ABA Routing Check Digit, SQL Development, SQL Server scripts and SQL Reporting,
Google

Main
How to Calculate Bank Routing Check Digit
 
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.

Article keywords: CREATE FUNCTION create_check_digit, return, substring(), isnumeric(), validate bank routing, ABA Check Digit, Routing Number, American Bankers Association, Federal Reserve Routing Symbol, Modules 10, Straight Summation


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.