## How to Validate Bank Routing Using Check Digit

 Author Message Al Siks Grasshopper Group: General Forum Members Points: 14 Visits: 27 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-Forever Group: General Forum Members Points: 45047 Visits: 39898 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...`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'        ENDgo` --Jeff ModenRBAR 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.Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.Although change is inevitable, change for the better is not.Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:How to post code problemsHow to post performance problemsForum FAQs o103452 SSC Rookie Group: General Forum Members Points: 46 Visits: 4439 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`