# How to Validate Bank Routing Using Check Digit

• Al Siks

Old Hand

Points: 360

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

Got SQL Server? Manage It with Siccolo

• Jeff Moden

SSC Guru

Points: 996807

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".
"If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

• o103452@rtrtr.com

Ten Centuries

Points: 1025

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 3 (of 3 total)