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

T-SQL-ACN-validation-test.sql

select * from (
	select '000 000 019' as acn, 1 as correct
	union all
	select '009 749 964' as acn, 1 as correct
	union all 
	select '006 999 980' as acn, 1 as correct
	union all
	select '005999977' as acn, 1 as correct 
	union all 
	select '000 000 019asdfsf' as acn, 0 as correct 
	union all 
	select '444' as acn, 0 as correct
	union all 
	select NULL as acn, 0 as correct 
	union all
	select '005999978' as acn, 0 as correct
	union all 
	select '' as acn, 0 as correct
) as t
where
        -- see https://asic.gov.au/for-business/registering-a-company/steps-to-register-a-company/australian-company-numbers/australian-company-number-digit-check/
	-- ACNs have 9 digits plus optional spaces. 9th digit is a checksum
  	replace(acn, ' ', '') like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
  	and
	right(10 - ((
            (8 *  substring(replace(acn, ' ', ''),1,1)) +
  	    (7 *  substring(replace(acn, ' ', ''),2,1)) +
            (6 *  substring(replace(acn, ' ', ''),3,1)) +
            (5 *  substring(replace(acn, ' ', ''),4,1)) +
            (4 *  substring(replace(acn, ' ', ''),5,1)) +
            (3 *  substring(replace(acn, ' ', ''),6,1)) +
            (2 *  substring(replace(acn, ' ', ''),7,1)) +
            (1 *  substring(replace(acn, ' ', ''),8,1))) ) % 10,1) = substring(replace(acn, ' ', ''),9,1)

Gives a result like:

acn		correct
000 000 019	1
009 749 964	1
006 999 980	1
005999977	1