SQL Server function to validate Australian ABN

During our Microsoft CRM 4 development a consultant implemented a plugin that calls a webservice to validate an ABN whenever this field was changed in CRM.

As I was doing the data migration of 10,000 plus customers and was going to have to validate this data before going into the new system rather than after and trapping an error I decided to use a SQL function to validate this data which also allowed me to identify bad records to be updated and could easily dump out to a spreadsheet for someone to fix.

Based on the rule for an Australian ABN that validates via a check digit on the number, I created a SQL Server function that I could use in my query to test at query time.

First the rules to verify an ABN:

1. Subtract 1 from the first (left) digit to give a new eleven digit number
2. Multiply each of the digits in this new number by its weighting factor
3. Sum the resulting 11 products
4. Divide the total by 89, noting the remainder
5. If the remainder is zero the number is valid

Weight factors (for Step 2)

SQL Server ABN Function

Usage:

Valid ABN for my favourite drink company - Red Bull Australia Pty Ltd :)

SELECT dbo.udf_IsValidABN(‘67 085 840 259’) as IsValid

Returns 1 as correct ABN

SELECT dbo.udf_IsValidABN(‘67085840259’) as IsValid

Returns 1 as Correct ABN without SPACES

SELECT dbo.udf_IsValidABN(‘69 085 840 259’) as IsValid

Returns 0 as Not a valid ABN

Here is the sql for the function.

CREATE FUNCTION [dbo].[udf_IsValidAbn] (@ABN VARCHAR(20))
RETURNS BIT
AS
  BEGIN

     -- Strip out spaces in ABN
      SELECT @ABN = REPLACE(@ABN, ' ', '')

      DECLARE @Valid BIT
      DECLARE @1 INT
      DECLARE @2 INT
      DECLARE @3 INT
      DECLARE @4 INT
      DECLARE @5 INT
      DECLARE @6 INT
      DECLARE @7 INT
      DECLARE @8 INT
      DECLARE @9 INT
      DECLARE @10 INT
      DECLARE @11 INT
      DECLARE @RESULT INT
      DECLARE @FINALRESULT INT

      SET @Valid = 0

      IF Len(@ABN) <> 11
        BEGIN
            RETURN @Valid
        END

      SET @1 = ( LEFT(@ABN, 1) - 1 ) * 10
      SET @2 = Substring(@ABN, 2, 1) * 1
      SET @3 = Substring(@ABN, 3, 1) * 3
      SET @4 = Substring(@ABN, 4, 1) * 5
      SET @5 = Substring(@ABN, 5, 1) * 7
      SET @6 = Substring(@ABN, 6, 1) * 9
      SET @7 = Substring(@ABN, 7, 1) * 11
      SET @8 = Substring(@ABN, 8, 1) * 13
      SET @9 = Substring(@ABN, 9, 1) * 15
      SET @10 = Substring(@ABN, 10, 1) * 17
      SET @11 = Substring(@ABN, 11, 1) * 19

      SET @RESULT = @1 + @2 + @3 + @4 + @5 + @6 + @7 + @8 + @9 + @10 + @11

      SET @FINALRESULT = @RESULT % 89

      IF @FINALRESULT = 0
        BEGIN
            SET @Valid = 1
        END

      RETURN @Valid
  END

GO

-- Don't forget to set appropriate permissions. As this is a standard calculation have set to public

GRANT EXECUTE ON [dbo].[udf_IsValidABN] TO [public] AS [dbo]

GO 

I also implemented the same thing to validate Australian company ACN as it used a slightly different algorithm. Will also post this function up if anyone is interested just reply in comments.