| Author |
Topic |
|
singularity
Posting Yak Master
153 Posts |
 Posted - 2008-04-16 : 13:20:30
|
| What's the easiest way of removing extra spaces from strings? For example, turning "John Smith" into "John Smith" and"Mary Brown" into "Mary Brown".Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-16 : 13:22:52
|
| You mean removing leading & trailing spaces? use LTRIM & RTRIM functions. |
 |
|
|
singularity
Posting Yak Master
153 Posts |
Posted - 2008-04-16 : 13:31:26
|
| No, I mean spaces between words. |
|
|
|
afrika
Master Smack Fu Yak Hacker
2706 Posts |
Posted - 2008-04-16 : 13:37:42
|
quote:
Originally posted by singularity
What's the easiest way of removing extra spaces from strings? For example, turning "John Smith" into "John Smith" and"Mary Brown" into "Mary Brown".Thanks
wHats the difference ? Do you mean shortening the spaces ? |
|
|
|
singularity
Posting Yak Master
153 Posts |
Posted - 2008-04-16 : 13:41:49
|
| If there is more than one space between words, I want to reduce it to one space. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-04-16 : 13:43:05
|
| Try with REPLACEREPLACE(field,' ',' ') |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-16 : 15:41:09
|
WHILE CHARINDEX(@Param, ' ') > 0 SET @Param = REPLACE(@Param, ' ', ' ')
E 12°55'05.25"N 56°04'39.16" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-16 : 15:47:51
|
lolWHILE CHARINDEX(@Param, ' ') > 0 SET @Param = REPLACE(@Param, ' ', ' ') ==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-16 : 15:52:45
|
The very same as I wrote, right?If you copy and paste, it shows incorrect but if you quote my response you will see that there are two spaces in the replace section.
E 12°55'05.25"N 56°04'39.16" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-04-16 : 15:56:34
|
| Yep - hence the lol.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-17 : 07:51:31
|
Here's a related and detailed discussion about this requirement...http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195One option is...select Replace(Replace(Replace(myColumn,' ',' þ'),'þ ',''),'þ','') Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-04-17 : 07:53:43
|
I wonder that our Icelandic friends has to say about that?SELECT REPLACE(REPLACE(REPLACE(myColumn,' ',' ' + CHAR(7)), CHAR(7) + ' ',''), CHAR(7), '')
E 12°55'05.25"N 56°04'39.16" |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2008-04-17 : 11:52:27
|
quote:
Originally posted by Peso
I wonder that our Icelandic friends has to say about that?
Actually, it's worse than that, as I pointed out in the referenced thread. |
|
|
|
|
Removing Extra Spaces