string manipulation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

string manipulation

Author

Topic

jeff06
Posting Yak Master

166 Posts

Posted - 2007-12-05 : 14:44:57

I have a table t with one column addresss.
address
Abilene TX
Abilene Tx
Abilene, Tx
Abilene. TX
Abilene.. TX

I want to covert all string to
ABILENE TX (Capitalize all letter and put one space in between)
How can I do that?
Thanks
Jeff

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 14:46:43

SELECT UPPER(REPLACE(REPLACE(REPLACE(Col1, '.', ' '), ',', ' '), ' ', ' '))
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

jeff06
Posting Yak Master

166 Posts

Posted - 2007-12-05 : 15:04:17

Thanks Peso,
I also want to remove the extra space between Abilene and TX the are only one space in between them.
how can i achive that?
Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-05 : 15:06:05

Ooops. Outermost REPLACE should change...

SELECT UPPER(REPLACE(REPLACE(REPLACE(Col1, '.', ' '), ',', ' '), '  ', ' '))
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

jeff06
Posting Yak Master

166 Posts

Posted - 2007-12-05 : 15:14:44

in 'Abilene TX' of my table
some have 1 space in between Abilene and TX
some have 2 space in between
some have 3 sapces in between
----
some have 10 spaces in bwtween
how I can remove the extra space and left one 1 in in between Abilene and TX for all cases?
Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-06 : 02:07:59

Select replace(replace(replace(col,' ',' ^'),'^ ',''),'^',' ') from table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources