parse string and update table in mysql

Question

parse string and update table in mysql

ix.lives

SSC Enthusiast

Points: 164
More actions
October 31, 2013 at 11:10 pm

#281884

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='%%';

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ed Wagner SSC Guru Points: 287026 More actions · Answer 1

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
String Splitting with True Performance
Best practices on how to ask questions

ix.lives SSC Enthusiast Points: 164 More actions · Answer 2

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.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 3

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.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions