How to Validate Bank Routing Using Check Digit

  • Al Siks

    Old Hand

    Points: 360

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


    Got SQL Server? Manage It with Siccolo

  • Jeff Moden

    SSC Guru

    Points: 996807

    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


    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.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • o103452@rtrtr.com

    Ten Centuries

    Points: 1025

    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