How to Validate Bank Routing Using Check Digit

,

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.
(for more scripts goto http://www.siccolo.com/articles.html)

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

Rate

1 (1)

Share

Share

Rate

1 (1)