September 29, 2007 at 8:53 pm
Comments posted to this topic are about the item How to Validate Bank Routing Using Check Digit
April 11, 2008 at 11:26 pm
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
Change is inevitable... Change for the better is not.
September 18, 2014 at 8:12 am
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)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy