How to Validate Bank Routing Using Check Digit

Question

How to Validate Bank Routing Using Check Digit

Al Siks

Old Hand

Points: 360
More actions
September 29, 2007 at 8:53 pm

#181689

Comments posted to this topic are about the item How to Validate Bank Routing Using Check Digit
Got SQL Server? Manage It with Siccolo

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

You made the mistake of using ISNUMERIC to check for digits. ISNUMERIC does not equal ISALLDIGITS... try this and see what I mean...

SELECT dbo.check_digit('072---326')

Rather than just have it return a check digit, why not have it validate the ABA routing number altogether? For example...

[font="Courier New"]DECLARE @RoutingNumber VARCHAR(8000) --Allows check of wrong size numbers

SET @RoutingNumber = '067803457'

SELECT CASE

WHEN LEN(@RoutingNumber)=9

AND @RoutingNumber NOT LIKE '%[^0-9]%' --Check to make sure is all digits

AND RIGHT(@RoutingNumber ,1)

= (--==== Calculates check digit (Digit 9)

SELECT 10-SUM(mv.DigitVal)%10 --Sum of individual digit values subtracted for new number ending in 0

FROM (--==== Gets multiplied value for each digit in 3, 7, 1 pattern

SELECT CASE

WHEN (t.N-1)%3 = 0 THEN SUBSTRING(@RoutingNumber,t.N,1)*3 --Digits 1,4,7

WHEN (t.N-1)%3 = 1 THEN SUBSTRING(@RoutingNumber,t.N,1)*7 --Digits 2,5,8

WHEN (t.N-1)%3 = 2 THEN SUBSTRING(@RoutingNumber,t.N,1)*1 --Digits 3,6

END AS DigitVal

FROM dbo.Tally t --See http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/

WHERE t.N <= 8

) mv

)

THEN 'Valid'

ELSE 'Not Valid'

END

go

[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

o103452@rtrtr.com Ten Centuries Points: 1025 More actions · Answer 2

Your snippet does not validate the routing number "111000960" -- or any other number I tried ending in 0. In this case, it rerturns 10 not 0.

So this seemed to work:

;WITH numbers AS ( select NUMBER as n from dbo.F_TABLE_NUMBER_RANGE(1,9) )

SELECT @isValid = CASE

WHEN LEN(@RoutingNumber)=9

AND @RoutingNumber NOT LIKE '%[^0-9]%' --Check to make sure is all digits

AND RIGHT(@RoutingNumber ,1) = right((

--==== Calculates check digit (Digit 9)

SELECT 10-SUM(mv.DigitVal)%10 --Sum of individual digit values subtracted for new number ending in 0

FROM (--==== Gets multiplied value for each digit in 3, 7, 1 pattern

SELECT CASE

WHEN (t.N-1)%3 = 0 THEN SUBSTRING(@RoutingNumber,t.N,1)*3 --Digits 1,4,7

WHEN (t.N-1)%3 = 1 THEN SUBSTRING(@RoutingNumber,t.N,1)*7 --Digits 2,5,8

WHEN (t.N-1)%3 = 2 THEN SUBSTRING(@RoutingNumber,t.N,1)*1 --Digits 3,6

END AS DigitVal

FROM numbers as t

WHERE t.N <= 8

) as mv

),1)

THEN 1

ELSE 0

END