

Grasshopper
Group: General Forum Members
Last Login: Thursday, April 10, 2014 9:33 AM
Points: 14,
Visits: 22





SSCDedicated
Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215,
Visits: 31,667


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('072326')
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 '%[^09]%' Check to make sure is all digits AND RIGHT(@RoutingNumber ,1) = (==== Calculates check digit (Digit 9) SELECT 10SUM(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.N1)%3 = 0 THEN SUBSTRING(@RoutingNumber,t.N,1)*3 Digits 1,4,7 WHEN (t.N1)%3 = 1 THEN SUBSTRING(@RoutingNumber,t.N,1)*7 Digits 2,5,8 WHEN (t.N1)%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
Jeff Moden "RBAR is pronounced "reebar" and is a "Modenism" for "RowByAgonizingRow".
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 TSQL, doesn't mean you SHOULDN'T." 22 Aug 2013
Helpful Links: How to post code problems How to post performance problems




Grasshopper
Group: General Forum Members
Last Login: Today @ 4:00 AM
Points: 11,
Visits: 1,719


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 '%[^09]%' Check to make sure is all digits AND RIGHT(@RoutingNumber ,1) = right(( ==== Calculates check digit (Digit 9) SELECT 10SUM(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.N1)%3 = 0 THEN SUBSTRING(@RoutingNumber,t.N,1)*3 Digits 1,4,7 WHEN (t.N1)%3 = 1 THEN SUBSTRING(@RoutingNumber,t.N,1)*7 Digits 2,5,8 WHEN (t.N1)%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



