MySQL - comma delimited fields?

[fretwizz]

I have a database where one of the columns is named "Address" and it contains addresses, unfortunately, they are complete addresses in one column (I know this is a mistake but it's what I have to work with). The various parts of the address in this column are separated by a comma... like so:

12345 Main St., Denver CO, 80202

I created a couple of new columns, one for the zip code and one for the city and I then copied the data from the existing address column into my new columns (it took a little bit of effort but nothing compared to going through the entire database and entering it manually). I suppose I can leave the address column the way it is now that I have the data in my new zip and city columns to refer to... but I'd like to learn a bit about MySQL's capabilities and thought this might be a good place to do that. I've been looking for a way to extract and copy based on the comma delimited concept but I can't seem to find it. I've run across things that look similar (that I have tried on a smaller, test database) but they haven't panned out. Any ideas on how this can be done or places I can research to solve this problem (if it's even possible)?

Thanks

 

[misson]

The place to look for MySQL's string manipulation capabilities is the string functions manual page. However, SQL isn't intended for string manipulation, hence MySQL's poor capabilities. A better solution would be to write a program in another language to perform the actual processing, perhaps using regular expressions.

/^\s*(\d+[^,]*\S)\s*,\s*(?:(?i)([^,]*(?:ste|apt|bldg|suite)[^,]*),(?i))?\s*([^,]+) ([A-Z]{2})\s*,\s*(\d{5}(?:-\d{4})?)\s*$/

 

[fretwizz]

Thanks for the info!