Validate Australian Business Number (ABN) in MySql 5.7 WHERE CLAUSE, including unit tests.

MySql-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, ' ', '') regexp '^[0-9]{11}$'
  	and
  	-- mod(x,89) must equal zero for a valid ABN.
	mod(
	    (10 * (mid(replace(abn, ' ', ''),1,1) - 1)) +
	    (1 *   mid(replace(abn, ' ', ''),2,1)) +
	    (3 *   mid(replace(abn, ' ', ''),3,1)) +
	    (5 *   mid(replace(abn, ' ', ''),4,1)) +
	    (7 *   mid(replace(abn, ' ', ''),5,1)) +
	    (9 *   mid(replace(abn, ' ', ''),6,1)) +
	    (11 *  mid(replace(abn, ' ', ''),7,1)) +
	    (13 *  mid(replace(abn, ' ', ''),8,1)) +
	    (15 *  mid(replace(abn, ' ', ''),9,1)) +
	    (17 *  mid(replace(abn, ' ', ''),10,1)) +
	    (19 *  mid(replace(abn, ' ', ''),11,1)), 89) = 0

Gives result like:

abn             correct
--------------  -------
43077535605           1
33 102 417 032        1
29002589460           1