parse string and update table in mysql
-
I need to parse a string in one column called "location" and copy the contents of "city,state" into 2 new columns of "city" and "state", while leaving location as it is, is this even possible? Or should I just move the data? I have a substring_index that works fine when I use the SELECT, but I need to update the table. thanks in advance, here is a copy of my substring_index code ...
select location,
substring_index(location, ' ',1) as city,
substring_index(location, ' ',-1) as state
from my_contacts
and here is a copy of the update, I am close....
UPDATE my_contacts SET
location=substring_index(location, ', ',1) as city,
WHERE location='%%';
UPDATE my_contacts
SET location=substring_index(location, ', ',-1) as state
from my_contacts
WHERE location='%%';
-
ix.lives (10/31/2013)
I need to parse a string in one column called "location" and copy the contents of "city,state" into 2 new columns of "city" and "state", while leaving location as it is, is this even possible? Or should I just move the data? I have a substring_index that works fine when I use the SELECT, but I need to update the table. thanks in advance, here is a copy of my substring_index code ...select location,
substring_index(location, ' ',1) as city,
substring_index(location, ' ',-1) as state
from my_contacts
and here is a copy of the update, I am close....
UPDATE my_contacts SET
location=substring_index(location, ', ',1) as city,
WHERE location='%%';
UPDATE my_contacts
SET location=substring_index(location, ', ',-1) as state
from my_contacts
WHERE location='%%';
Hello and welcome to the forums. To write a solution we need some readily-consumable DDL and data, so I wrote some for you as follows:
create table #temp (location varchar(255),city varchar(120),state varchar(120));insert into #temp(location) values('Ann Arbor, MI');insert into #temp(location) values('Bloomfield Hills, MI');insert into #temp(location) values('Troy, MI');insert into #temp(location) values('Novi, MI');I don't know of a substring_index function in 2008. Here's an update statement that will do what you're looking for:
update #tempset city = substring(location, 1, charindex(',', location) - 1),state = right(location, len(location) - charindex(',', location) - 1);For a very efficient approach to string parsing, please read Jeff Moden's article at http://www.sqlservercentral.com/articles/Tally+Table/72993/. It's very efficient and the performance on large data sets will make you scratch your head.
-
there is a substring_index function in MySQL, what is interesting is when I tried your code I get a syntax err msg stating "unknown column city in field list" and its in the table sooooo, this is why I hate programming! all I need to do is modify the table, and I know there is a workable solution, I've seen it thank you though.
-
ix.lives (11/1/2013)
there is a substring_index function in MySQL, what is interesting is when I tried your code I get a syntax err msg stating "unknown column city in field list" and its in the table sooooo, this is why I hate programming! all I need to do is modify the table, and I know there is a workable solution, I've seen it thank you though.My mistake - I completely missed the MySQL in the subject. I figured it was a SQL Server question. I guess I should slow down and read the subject as well as the body of the post. :crazy:
MySQL should have a way to do this, but I don't know MySQL at all, so I don't know where to direct you from here.
-
update my_contacts
set city = substring_index(location, ',', 1),
state = substring_index(location, ',',-1) ;
solves the problem!
-
how do I mark a question as solved?
-
I don't believe you have to. Just telling us it's solved is "closed" enough
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply