Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

parse string and update table in mysql Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:29 AM
Points: 4, Visits: 3
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='%%';
Post #1510497
Posted Friday, November 01, 2013 6:23 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 3,297, Visits: 2,340
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 #temp
set 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.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1510564
Posted Friday, November 01, 2013 7:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:29 AM
Points: 4, Visits: 3
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.
Post #1510596
Posted Friday, November 01, 2013 8:31 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:12 PM
Points: 3,297, Visits: 2,340
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.

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.



Tally Tables - Performance Personified
Best practices on how to ask questions
Post #1510611
Posted Friday, November 01, 2013 10:15 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:29 AM
Points: 4, Visits: 3
update my_contacts
set city = substring_index(location, ',', 1),
state = substring_index(location, ',',-1) ;

solves the problem!
Post #1510687
Posted Friday, November 01, 2013 10:27 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 14, 2013 10:29 AM
Points: 4, Visits: 3
how do I mark a question as solved?
Post #1510695
Posted Monday, November 04, 2013 4:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Today @ 6:57 AM
Points: 63, Visits: 364
I don't believe you have to. Just telling us it's solved is "closed" enough
Post #1511050
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse