select * from STRING_SPLIT(replace('Wilson Photography<br>333 54th Ave. North<br>Nashville TN 37209', '<br>', '~') , '~' )
That will return three rows:declare @table table (col varchar(100))
insert @table values
('Wilson Photography<br>333 54th Ave. North<br>Nashville TN 37209'),
('Wyatt Archaelogical Research<br>4313 Green Hills Rd<br>Nashville TN 37174-5132'),
('Ed Perdue<br>311 Hobbs Dr<br>White House TN 37188'),
(NULL),
(NULL),
('Maury Wahtera<br>407 Normandy Circle<br>Nashville TN 37209')
;with cte1 as (select *,charindex('<br>',col) idx1, charindex('<br>', col, charindex('<br>',col)+1) idx2 from @table),
cte2 as (select col,left(col,idx1-1) Name, substring(col,idx1+4,idx2-idx1-4) Address,substring(col,idx2+4,len(col)) str3 from cte1),
cte3 as (select *,reverse(str3) rev_str3 from cte2),
cte4 as (select *,charindex(' ',rev_str3) idx1, charindex(' ',rev_str3,charindex(' ',rev_str3)+1) idx2 from cte3)
select Name, Address,
reverse(rtrim(ltrim(substring(rev_str3,idx2,len(rev_str3))))) City,
reverse(rtrim(ltrim(substring(rev_str3,idx1,idx2-idx1)))) State,
reverse(left(rev_str3,idx1-1)) ZipCode
from cte4
/*
Name Address City State ZipCode
Wilson Photography 333 54th Ave. North Nashville TN 37209
Wyatt Archaelogical Research 4313 Green Hills Rd Nashville TN 37174-5132
Ed Perdue 311 Hobbs Dr White House TN 37188
NULL NULL NULL NULL NULL
NULL NULL NULL NULL NULL
Maury Wahtera 407 Normandy Circle Nashville TN 37209
*/