Split MYSQL column into multiple columns

Question

I have a few million records in a mysql database with the following columns: company, address, url, phone, category

Here is a sample row: Company123 - 123 Candyland St, New York, NY 12345 - http://urltothiscompany.com - 123-456-7890 - Bakery

My question is about the address column. I'd like to split the rows up into separate address, city, state, and zip code columns: 123 Candyland St - New York - NY - 12345

However, some rows don't have a street, only city, state, and zip: New York, NY, 1235

Is there a possible way to do that in mysql? I'm not sure where to begin since some rows don't have the address. Maybe count the characters from the end of the column?

Any help is appreciated. Thank you.

Answer 1

Assume your data is actually looking like following:

addr
=======================
street, City, State ZIP

And here is the SQL:

SELECT addr,
  substr(addr, 1, length(addr) - length(substring_index(addr, ',', -2))) street,
  substring_index(substring_index(addr, ',', -2), ',', 1) city,
  substr(trim(substring_index(addr, ',', -1)),1,2) state,
  substring_index(addr, ' ', -1) zip
FROM tab

OOPs there is an extra comma at street, this is a homework for you to fix :)