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.