Validate Australian Business Number (ABN) in T-SQL for SQL Server WHERE clause, including test cases

T-SQL-ABN-validation-test.sql

select * from (
    select '43077535605' as abn, 1 as correct
    union all
    select '33 102 417 032' as abn, 1 as correct
    union all 
    select '29002589460' as abn, 1 as correct 
    union all 
    select '33 102 417 032asdfsf' as abn, 0 as correct 
    union all 
    select '444' as abn, 0 as correct
    union all 
    select NULL as abn, 0 as correct 
    union all
    select '35121729589' as abn, 0 as correct
    union all 
    select '' as abn, 0 as correct
) as t
where
      -- see https://abr.business.gov.au/Help/AbnFormat
      -- ABNs have 11 digits plus optional spaces
      replace(abn, ' ', '') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
      and
      -- "x % 89" must equal zero for a valid ABN.
    (
        (10 * (substring(replace(abn, ' ', ''),1,1) - 1)) +
        (1 *   substring(replace(abn, ' ', ''),2,1)) +
        (3 *   substring(replace(abn, ' ', ''),3,1)) +
        (5 *   substring(replace(abn, ' ', ''),4,1)) +
        (7 *   substring(replace(abn, ' ', ''),5,1)) +
        (9 *   substring(replace(abn, ' ', ''),6,1)) +
        (11 *  substring(replace(abn, ' ', ''),7,1)) +
        (13 *  substring(replace(abn, ' ', ''),8,1)) +
        (15 *  substring(replace(abn, ' ', ''),9,1)) +
        (17 *  substring(replace(abn, ' ', ''),10,1)) +
        (19 *  substring(replace(abn, ' ', ''),11,1))
    ) % 89 = 0

gives output like:

abn		correct
43077535605	1
33 102 417 032	1
29002589460	1