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: Thursday, April 10, 2014 9:33 AM Points: 14, Visits: 22
 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-Dedicated Group: General Forum Members Last Login: Today @ 12:02 AM Points: 37,451, Visits: 34,307
 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." (play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013Helpful Links:How to post code problemsHow to post performance problems
Post #484035
 Posted Thursday, September 18, 2014 8:12 AM
 Grasshopper Group: General Forum Members Last Login: Yesterday @ 9:18 PM Points: 23, Visits: 2,816
 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