# How to Validate Bank Routing Using Check Digit

• Comments posted to this topic are about the item How to Validate Bank Routing Using Check Digit

Got SQL Server? Manage It with Siccolo

• 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.

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

• 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`

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