Generate Full Bank ABA Routing Number given the Federal Reserve Routing Symbol and ABA Institution Identifier

By Allen Stoner

A bank ABA number our routing number is made up of three numbers, the federal reserve routing symbol (4 digits), the ABA institution indentifier (4 digits) and a check digit (1 digit). This SQL stored procedure returns the full bank routing number given the first two parts.

Create PROCEDURE [dbo].[GenerateFullABA]
@ABABank varchar(4) = 0,
AS
BEGIN
SET NOCOUNT ON;

DECLARE @routing_number VARCHAR(8)
SET @routing_number = replicate('0', 4 - Len(@ABABank)) + @ABABank + replicate('0', 4 - Len(@ABADistrict)) + @ABADistrict

declare @digit int
declare @sum_digit int
declare @check_digit int

set @check_digit=0
-- Implementation of the algorithm found at the following link
--http://en.wikipedia.org/wiki/Routing_transit_number  (under Check digit section)
--1, 4, 7
set @digit =convert(int, substring(@routing_number, 1, 1)) + convert(int, substring(@routing_number, 4, 1)) + convert(int, substring(@routing_number, 7, 1))
set @sum_digit = @digit*7
--2, 5, 8
set @digit =convert(int, substring(@routing_number, 2, 1)) + convert(int, substring(@routing_number, 5, 1)) + convert(int, substring(@routing_number, 8, 1))
set @sum_digit = @sum_digit + @digit*3
--3, 6
set @digit =convert(int, substring(@routing_number, 3, 1))
set @sum_digit = @sum_digit + @digit*9

set @check_digit = 10 - (@sum_digit % 10)

Return @routing_number + LTRIM(str(@check_digit))
END

Generate Full Bank ABA Routing Number given the Federal Reserve Routing Symbol and ABA Institution Identifier  (3134 Views)