CODEWITHSUNDEEP
phpmysql
dkeeper09
dkeeper09

Reputation: 547

Split MYSQL column into multiple columns

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.

Upvotes: 6

Views: 13562

Answers (2)

Hebe
Hebe

Reputation: 800

Quick answer for googlers as me.
Example splitting columns into 3. If you need 2 columns omit new_column_name_2 inside requests.
Works for dimensions like 100x200x300 becomes 100 200 300

  1. Create new columns.
ALTER TABLE `your_table_name` ADD `new_column_name_1` VARCHAR(250) DEFAULT '';
ALTER TABLE `your_table_name` ADD `new_column_name_2` VARCHAR(250) DEFAULT '';
ALTER TABLE `your_table_name` ADD `new_column_name_3` VARCHAR(250) DEFAULT '';
  1. Verify you're selecting correct stuff
SELECT `old_column_name`,
SUBSTRING_INDEX(`old_column_name`, 'x', 1) `new_column_name_1`,
SUBSTRING_INDEX(SUBSTRING_INDEX(`old_column_name`, 'x', 2),'x',-1) `new_column_name_2`, 
SUBSTRING_INDEX(`old_column_name`, 'x', -1) `new_column_name_3`
FROM `your_table_name`;
  1. Populate new columns
UPDATE `your_table_name` SET
`new_column_name_1` = SUBSTRING_INDEX(`old_column_name`, 'x', 1),
`new_column_name_2` = SUBSTRING_INDEX(SUBSTRING_INDEX(`old_column_name`, 'x', 2),'x',-1),
`new_column_name_3` = SUBSTRING_INDEX(`old_column_name`, 'x', -1);

Upvotes: 3

SIDU
SIDU

Reputation: 2278

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

enter image description here

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

Upvotes: 10

Related Questions