November 30, 2012 at 11:54 am
Is there a way to replace any value with a black space or nothing after a comma. For example, lets say I have Atlanta, GA in a city field and I just want Atlanta to show. I tired the following but it doesn't work. I know the % is a wild card when using a like clause, but how would this work using update with a replace?
Update table.address
set city = replace (city,',%',' ')
Thanks
November 30, 2012 at 12:16 pm
Something like this:
DECLARE @TestStr VARCHAR(20) = 'Atlanta, GA';
SELECT
@TestStr,
CHARINDEX(',',@TestStr),
LEN(@TestStr),
LEN(@TestStr) - CHARINDEX(',',@TestStr),
STUFF(@TestStr,CHARINDEX(',',@TestStr),LEN(@TestStr),'');
November 30, 2012 at 12:20 pm
Will that work even if there are multiple city names and not just Atlanta?
November 30, 2012 at 12:23 pm
Or an even simpler way given your original post.
;with cte(FullName) as
(
select 'Atlanta, GA'
)
select LEFT(FullName, charindex(',', FullName) - 1)
from cte
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2012 at 12:24 pm
justin_post (11/30/2012)
Will that work even if there are multiple city names and not just Atlanta?
Do you mean you more than one City, ST in the column? Is it delimited at all?
'Atlanta, GA:Seattle, WA'
If you can post a more detailed explanation of what you are working with we can help.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2012 at 12:29 pm
Yes there are multiple cities and it is delimited at all. Here is just an example of a few:
Augusta, GA
Denver, Colorado
Trenton, Nj
Silao, Guanajuato
Sioux Falls, Avera Hospital
Reno, Nevada
Fort Bragg, Womack Army
Hyderabad, India
Dagupan City, Phillipines
Bloomington, Il
Bossier City, La
Maricora, Arizona
Silvas, Ilinni Hospital
Abilene, Texas
Southfield, Providence
St Louis, St John Hospital
Akron, Summa Health
I would just need to replace anything after the comma with nothing or a blank space. That why I wasn't sure if you could use some type of like ='%,%' to replace the commas with nothing. I don't think there is
November 30, 2012 at 12:35 pm
This:
DECLARE @TestTable TABLE (
City VARCHAR(64)
);
INSERT INTO @TestTable
( City )
VALUES
('Augusta, GA'),
('Denver, Colorado'),
('Trenton, Nj'),
('Silao, Guanajuato'),
('Sioux Falls, Avera Hospital'),
('Reno, Nevada'),
('Fort Bragg, Womack Army'),
('Hyderabad, India'),
('Dagupan City, Phillipines'),
('Bloomington, Il'),
('Bossier City, La'),
('Maricora, Arizona'),
('Silvas, Ilinni Hospital'),
('Abilene, Texas'),
('Southfield, Providence'),
('St Louis, St John Hospital'),
('Akron, Summa Health');
select LEFT(City, charindex(',', City) - 1)
from @TestTable;
November 30, 2012 at 12:36 pm
Well since what you posted still doesn't really explain what you are working with I turned this into something usable.
Does your table look something like this?
;with cte(FullName) as
(
select 'Augusta, GA' union all
select 'Denver, Colorado' union all
select 'Trenton, Nj' union all
select 'Silao, Guanajuato' union all
select 'Sioux Falls, Avera Hospital' union all
select 'Reno, Nevada' union all
select 'Fort Bragg, Womack Army' union all
select 'Hyderabad, India' union all
select 'Dagupan City, Phillipines' union all
select 'Bloomington, Il' union all
select 'Bossier City, La' union all
select 'Maricora, Arizona' union all
select 'Silvas, Ilinni Hospital' union all
select 'Abilene, Texas' union all
select 'Southfield, Providence' union all
select 'St Louis, St John Hospital' union all
select 'Akron, Summa Health'
)
select LEFT(FullName, charindex(',', FullName) - 1)
from cte
The code I already posted will work just fine. If your table is not in that kind of format you need to post ddl and sample data so I know what you are working with. You should take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 30, 2012 at 12:38 pm
Or, if you are doing an update, look at the end of this:
DECLARE @TestTable TABLE (
City VARCHAR(64)
);
INSERT INTO @TestTable
( City )
VALUES
('Augusta, GA'),
('Denver, Colorado'),
('Trenton, Nj'),
('Silao, Guanajuato'),
('Sioux Falls, Avera Hospital'),
('Reno, Nevada'),
('Fort Bragg, Womack Army'),
('Hyderabad, India'),
('Dagupan City, Phillipines'),
('Bloomington, Il'),
('Bossier City, La'),
('Maricora, Arizona'),
('Silvas, Ilinni Hospital'),
('Abilene, Texas'),
('Southfield, Providence'),
('St Louis, St John Hospital'),
('Akron, Summa Health');
select LEFT(City, charindex(',', City) - 1)
from @TestTable;
UPDATE @TestTable SET
City = LEFT(City, charindex(',', City) - 1)
WHERE
charindex(',', City) > 0;
SELECT * FROM @TestTable;
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply