I need to take an address that consists of "city, state" from column "location" and populate 2 new columns "city" and "state" but leave location the way it is, now I have done this with a SUBSTRING_INDEX command but I have to run the command everytime to do this, how can I make it stick?
thanks in advance. also here is my substring code....
SELECT distinct id, first_name, last_name,
SUBSTRING_INDEX(location, ' ,', 1) AS City,
SUBSTRING_INDEX(location, ' ,', -1) AS State,
SUBSTRING_INDEX(seeking, ' ,', 1) AS Seeking_1,
SUBSTRING_INDEX(seeking, ' ,', -1) AS Seeking_2,
SUBSTRING_INDEX(interests,' ,', 1) AS Interests_1,
SUBSTRING_INDEX(interests,' ,', -1) AS Interests_2,
SUBSTRING_INDEX(interests,' ,', 1) AS Interests_3
FROM my_contacts