substring/charindex grappler!

I've been working on this statement for 2 hours. I need another pair of techie-eyes to review this before I wander even farther into the jungle growth I've created for myself!;-) If there's a better way to achieve the same goal without using (overusing) the charindex/substring function combination please let me know!;-)

Here's the goal-- I have thousands of denormalized addresses (along with other contact info) arriving in a text file that look like:
 
28944 Timberlane,,Agoura Hills,CA,91301
32 Union Square East, Ste 608,New York, NY, 10003

which I am trying to migrate into the columns: address1, address2,city,state,postalCode in a normalized table.

Here is an example of what I've been able to retrieve so far with the current t-sql statement I created (see code snippet):

32 Union Square East (address1) - correct
Ste 608 (address2) - correct
New York, NY (City) - wrong
NULL (state) -  Here's where I got really lost and decided to post this!;-)
10003 (postalCode) - correct

select 
  last,
  first,
  address,
  LEFT(address, CHARINDEX(',', address) - 1) as 'Address1',
  SUBSTRING(address, CHARINDEX(',',address) + 1, CHARINDEX(',',address, CHARINDEX(',',address) + 1) - (CHARINDEX(',', address) + 1)) as 'Address2',
  SUBSTRING(address, CHARINDEX(',',address, CHARINDEX(',',address) + 1) + 1, DATALENGTH(address) - CHARINDEX(',', address, CHARINDEX(',', address) + 1) - CHARINDEX(',', REVERSE(address))) as 'City',
  ltrim(RIGHT(address, CHARINDEX(',', REVERSE(address)) - 1)) as 'PostalCode', 
  phone,
  mobile,
  fax,
  States
from sw_salesforce
where website is not null 
 and  address is not null

Select allOpen in new window