How to Validate Bank Routing Using Check Digit

• Al Siks

  Old Hand

  Points: 360

  More actions

  September 29, 2007 at 8:53 pm

  #181689

  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: 1004595

  More actions

  April 11, 2008 at 11:26 pm

  #801776

  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.
  

  ---

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

• o103452@rtrtr.com

  Ten Centuries

  Points: 1025

  More actions

  September 18, 2014 at 8:12 am

  #1746413

  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