I have this list of data:
ADDRESS
'204 W 8th St, ABC, New York, NY 12345-6789'
'222 N Barley St, Pittsburh, Pennsylvania, PA 98765-4321'
'1 S Main St, Good Day, Washington, PA 13579-2468'
'232 Justin Blvd, Sacramento, CA 86420-7531'
Where I would like to parse into 5 fields ie Mail Address 1, Mail Address 2, City, State, Zip Code. I been able to parse some of these individual though not correctly such as:
select distinct StreetName =
substring(ADDRESS, CHARINDEX(',', ADDRESS+',', 1) +1,
CHARINDEX(',', ADDRESS+',', CHARINDEX(',', ADDRESS+',', 1) +1) -
CHARINDEX(',', ADDRESS+',', 1) -1)
from Bills
where ISNUMERIC(LEFT(ADDRESS,1))=1
AND LEN(ADDRESS) > 1
which gets me mail address 2 mostly. How can I do this so that the string is separated into 5 columns?