Need T-SQL to UPDATE column w/ inserted COMMA

Question

Post reply

Need T-SQL to UPDATE column w/ inserted COMMA

Express12

SSCoach

Points: 18514
More actions
March 24, 2005 at 6:56 am

#67177

Looking for T-SQL to add a COMMA in the following situation:
My single column contains the City, State, Country values. Unfortunately, the Country is sometimes preceded w/ spaces instead of a comma:
Chicago, IL, USA <-- this is CORRECT format
Chicago, IL USA <-- this is INCORRECT format (no COMMA b4 USA)
Need T-SQL to interrogate this column and UPDATE it w/ a COMMA before Country to look like:
Chicago, IL, USA
BT

Viewing 10 posts - 1 through 9 (of 9 total)

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

Adam Machanic SSCoach Points: 15370 More actions · Answer 1

A simple -- if naive -- approach, might be to take advantage of the fact that there are two spaces:

UPDATE YourTable

SET CityStateCountry = REPLACE(CityStateCountry, ' ', ', ')

WHERE CHARINDEX(' ', CityStateCountry) > 0

--
Adam Machanic
whoisactive

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 2

Are they always double-spaced?

You could try

UPDATE TEST_CUST

SET COMBO_COL = REPLACE(COMBO_COL, ' ', ', ')

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

If you can ensure the string *always* contains 'USA', this might work

declare @string varchar(20)

set @string = 'Chicago, IL USA'

select replace(rtrim(ltrim(@string)), ' USA',', USA')

-----------------------

Chicago, IL, USA

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Adam Machanic SSCoach Points: 15370 More actions · Answer 4

Anyone else thinking this might be a good candidate for an example of why display should be done on the client side and atomic attributes should be placed into their own columns?

--
Adam Machanic
whoisactive

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

I like to think that this is for data cleansing to get rid of 1NF violation

I'm going home now.

Happy Easter to you all!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 6

I agree wholeheartedly.

We have a servicer/vendor supplied database (comes off a mainframe) that has an address table the is separate adress data addr_ln_1, addr_ln_2, city, state, zip with possible multiple adresses per customer (summer addr, winter addr, PO Box, etc). Another table in the DB has it as mailing adress addr_line_1, addr_line_2, addr_line_3, addr_line_4 (generally 4 is the CSZ) but only one to a customer. We had a developer that kept trying to use the mailing adress table.

The number of times I wanted to reach over and slap him across the back of the head.....

He is no longer with the company

Can you redevelop the table and feed a view to the end application?

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Vladan SSC-Insane Points: 22072 More actions · Answer 7

Happy Easter, Frank!

BTW, your code adds a comma even if one is already there... Something like this should work better, but still it depends on whether there really is 'USA' at the end.

declare @string varchar(20)

set @string = 'Chicago, IL, USA'

IF LEFT(RIGHT(@string,5),1) <> ','

select replace(rtrim(ltrim(@string)), ' USA',', USA')

Robert Davis One Orange Chip Points: 28027 More actions · Answer 8

I agree that this should be getting treated as 3 separate fields, not 1.

Nonetheless, a simpler variation of the above "Replace" solutions would be to remove all commas and then replace a space with a comma and a space.

Declare @string varchar(20)

Set @string = 'Chicago, IL, USA'

Set @string = Replace(Replace(@string, ',', ''), ' ', ', ')

Of course this breaks down for cities like New York, Des Moines, etc., or if they enter the full state name for New Mexico, New York, etc., or the full country name.

So if they enter "Truth or Consequences, New Mexico, United States of America", you'd end up with "Truth, or, Consequences, New, Mexico, United, States, of, America".

My blog: SQL Soldier[/url]
SQL Server Best Practices: SQL Server Best Practices
Twitter: @SQLSoldier
My book: Pro SQL Server 2008 Mirroring[/url]
Microsoft Certified Master: SQL Server, Data Platform MVP
Database Engineer at BlueMountain Capital Management[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Doh, I've missed this "sometimes" and had thought that there is never a comma. Thanks for catching that!

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]