0

I have a table named my_jobs that I am trying to copy a column named "location" into two other columns called "city" and "state." The city and state data need to be extracted in their own respective columns. The city and state columns are already created. The "location" column has multiple different city and state information separated by a comma (Mountain View, CA). I've tried using:

Replace INTO my_jobs (city)
SELECT substring_index(location, ", ",1) 
FROM my_jobs;

But that adds new rows and doesn't merge the data into the existing fields.

2

You need to update your table not insert

update my_jobs set city=substring_index(location, ", ",1) ,
state=SUBSTRING(location, LENGTH(location)-2, LENGTH(location))

If you want more explanation add a comment

5
  • 1
    Thanks but I forgot to mention there are multiple different values in the location column, that would replace only one row correct?
    – axxic3
    Feb 3 '18 at 0:41
  • Sorry, I made a test first and forgotten to change the code :D
    – Krismorte
    Feb 3 '18 at 0:44
  • How would I select the cities if they are varying lengths?
    – axxic3
    Feb 3 '18 at 1:10
  • What your mean select? I use SUBSTRING, LENGTH and substring_index funcitons to find the size, position and extract the text w3resource.com/mysql/string-functions/mysql-length-function.php techonthenet.com/mysql/functions/substring.php w3schools.com/sql/func_mysql_substring_index.asp
    – Krismorte
    Feb 3 '18 at 1:15
  • 1
    I edited your script to just substring index and that worked for the cities. The issue I was having is that the cities dont have a fixed position like a two character state abbreviation would. I set the substring index to use "comma space" as a delimiter and I didn't have to set a character length. Thanks for your help!
    – axxic3
    Feb 3 '18 at 7:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.