Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to Validate Bank Routing Using Check Digit Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, September 29, 2007 8:53 PM
 Grasshopper Group: General Forum Members Last Login: Wednesday, October 14, 2015 5:06 AM 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
Post #404540
 Posted Friday, April 11, 2008 11:26 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 11:17 AM Points: 42,045, Visits: 39,424
 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 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." Helpful Links:How to post code problemsHow to post performance problems
Post #484035
 Posted Thursday, September 18, 2014 8:12 AM
 SSC Rookie Group: General Forum Members Last Login: Yesterday @ 10:50 AM Points: 46, Visits: 4,414
 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`
Post #1614921

 Permissions

 Copyright © 2002-2016 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.