• 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.