SQL: Explode address text column into separate columns

gistfile1.sql

select 
length(replace(address_str, '\r\n', '\r\n ')) - length(address_str) as AddressLineCount,    
  substring_index(substring_index(address_str,'\r\n',1),'\r\n',-1)  as AddressLine1,
  substring_index(substring_index(address_str,'\r\n',2),'\r\n',-1)  as AddressLine2,
  substring_index(substring_index(address_str,'\r\n',3),'\r\n',-1)  as AddressLine3
  
from (
    select replace(concat(address,'\r\n'),'\r\n\r\n','\r\n') as address_str
	from table_location where location_id = 1
) normalized