RE: How to Validate Bank Routing Using Check Digit

Jeff Moden — Fri, 11 Apr 2008 23:26:30 GMT

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...[code][font="Courier New"][size="2"][color="blue"]DECLARE [/color][color="#434343"]@RoutingNumber [/color][color="blue"]VARCHAR[/color][color="gray"]([/color][color="black"]8000[/color][color="gray"]) [/color][color="green"]--Allows check of wrong size numbers [/color][color="blue"]SET [/color][color="#434343"]@RoutingNumber [/color][color="blue"]= [/color][color="red"]'067803457' [/color][color="blue"]SELECT [/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="magenta"]LEN[/color][color="gray"]([/color][color="#434343"]@RoutingNumber[/color][color="gray"])[/color][color="blue"]=[/color][color="black"]9 [/color][color="gray"]AND [/color][color="#434343"]@RoutingNumber [/color][color="gray"]NOT LIKE [/color][color="red"]'%[^0-9]%' [/color][color="green"]--Check to make sure is all digits [/color][color="gray"]AND [/color][color="magenta"]RIGHT[/color][color="gray"]([/color][color="#434343"]@RoutingNumber [/color][color="gray"],[/color][color="black"]1[/color][color="gray"]) [/color][color="blue"]= [/color][color="gray"]([/color][color="green"]--==== Calculates check digit (Digit 9) [/color][color="blue"]SELECT [/color][color="black"]10[/color][color="gray"]-[/color][color="magenta"]SUM[/color][color="gray"]([/color][color="black"]mv.DigitVal[/color][color="gray"])%[/color][color="black"]10 [/color][color="green"]--Sum of individual digit values subtracted for new number ending in 0 [/color][color="blue"]FROM [/color][color="gray"]([/color][color="green"]--==== Gets multiplied value for each digit in 3, 7, 1 pattern [/color][color="blue"]SELECT [/color][color="magenta"]CASE [/color][color="blue"]WHEN [/color][color="gray"]([/color][color="black"]t.N[/color][color="gray"]-[/color][color="black"]1[/color][color="gray"])%[/color][color="black"]3 [/color][color="blue"]= [/color][color="black"]0 [/color][color="blue"]THEN [/color][color="magenta"]SUBSTRING[/color][color="gray"]([/color][color="#434343"]@RoutingNumber[/color][color="gray"],[/color][color="black"]t.N[/color][color="gray"],[/color][color="black"]1[/color][color="gray"])*[/color][color="black"]3 [/color][color="green"]--Digits 1,4,7 [/color][color="blue"]WHEN [/color][color="gray"]([/color][color="black"]t.N[/color][color="gray"]-[/color][color="black"]1[/color][color="gray"])%[/color][color="black"]3 [/color][color="blue"]= [/color][color="black"]1 [/color][color="blue"]THEN [/color][color="magenta"]SUBSTRING[/color][color="gray"]([/color][color="#434343"]@RoutingNumber[/color][color="gray"],[/color][color="black"]t.N[/color][color="gray"],[/color][color="black"]1[/color][color="gray"])*[/color][color="black"]7 [/color][color="green"]--Digits 2,5,8 [/color][color="blue"]WHEN [/color][color="gray"]([/color][color="black"]t.N[/color][color="gray"]-[/color][color="black"]1[/color][color="gray"])%[/color][color="black"]3 [/color][color="blue"]= [/color][color="black"]2 [/color][color="blue"]THEN [/color][color="magenta"]SUBSTRING[/color][color="gray"]([/color][color="#434343"]@RoutingNumber[/color][color="gray"],[/color][color="black"]t.N[/color][color="gray"],[/color][color="black"]1[/color][color="gray"])*[/color][color="black"]1 [/color][color="green"]--Digits 3,6 [/color][color="blue"]END AS [/color][color="black"]DigitVal [/color][color="blue"]FROM [/color][color="black"]dbo.Tally t [/color][color="green"]--See http://www.sqlservercentral.com/scripts/Advanced+SQL/62486/ [/color][color="blue"]WHERE [/color][color="black"]t.N [/color][color="gray"]<= [/color][color="black"]8 [/color][color="gray"]) [/color][color="black"]mv [/color][color="gray"]) [/color][color="blue"]THEN [/color][color="red"]'Valid' [/color][color="blue"]ELSE [/color][color="red"]'Not Valid' [/color][color="blue"]END[/color][color="black"]go[/color][/size][/font][/code]

How to Validate Bank Routing Using Check Digit

Al Siks — Sat, 29 Sep 2007 20:53:13 GMT

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

SQLServerCentral / Article Discussions / Discuss content posted by Al Siks / Article Discussions by Author / How to Validate Bank Routing Using Check Digit / Latest Posts

RE: How to Validate Bank Routing Using Check Digit

How to Validate Bank Routing Using Check Digit