Validate Australian Company Number (ACN) in MySql 5.7 WHERE clause, including test cases

MySql-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, ' ', '') regexp '^[0-9]{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 result like

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