Removing spaces - 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)

Removing spaces

Author

Topic

hendrasd
Starting Member

20 Posts

Posted - 2002-11-11 : 22:30:46

Is there any function in SQL Server to remove spaces between two words, so that it contains just one space between two words?
For example : 'one two' contains 3 spaces between the two words. The result must be 'one two' that contains just one space.

-- Never stop learning --

simondeutsch
Aged Yak Warrior

547 Posts

Posted - 2002-11-11 : 22:53:49

There is no single function if the words are in one field, but you can use nested functions to get the result.
SUBSTRING(fieldname,1,charindex(' ',fieldname)) + ' ' + LTRIM(Substring(fieldname,charindex(' ',fieldname),len(fieldname) - charindex(' ',fieldname)))

Sarah Berger MCSD

jsarraf
Starting Member

2 Posts

Posted - 2002-11-11 : 23:51:34

Unfortunatly SQL does not have a function for exhaustive replace or regular expression replacement.

However you can use the method above which is perfect if you only have two tokens in your string, if you have more than that then you need to use the REPLACE function inside a While loop.

Example :
Declare
@manyWords varchar(100),
@twoSpaces varchar(2),
@oneSpace varchar(1)

Set @manyWords = 'Removing many spaces from a string'
Set @twoSpaces = ' '
Set @oneSpace = ' '
While(CharIndex(@twoSpaces, @manyWords,1)>0)
BEGIN
Set @manyWords = Replace(@manyWords,@twoSpaces,@oneSpace)
END

Select @manyWords

Good Luck

WWJD

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-12 : 04:58:20

Since varchar has a maximum length of 8000, you can guarantee that only 13 (2^13 = 8192) nested replaces are ever required.


REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
REPLACE(colname,'  ',' '),'  ',' '),'  ',' '),
'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' '),
'  ',' '),'  ',' '),'  ',' '),'  ',' '),'  ',' ')

Andraax
Aged Yak Warrior

790 Posts

Posted - 2002-11-12 : 05:03:44

Nice Arnold.... Never would have thought of that (duh...)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2002-11-12 : 05:52:42

Actually, 6 is sufficient.


REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(colname,
REPLICATE(' ',30),' '),REPLICATE(' ',12),' '),
'    ',' '),'   ',' '),'  ',' '),'  ',' ')

Even this may not be the best possible, but proving that is too tricky for me!

Edit: I'm pretty convinced now that it requires 6 REPLACEs. I think that, using 5 REPLACEs it is possible to collapse up to 461 spaces (using the sequence* 21, 6, 3, 2, 2). With 6 REPLACEs, this increases to 53591 (prepending 231 to the sequence).

* Like this:


REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(colname,
REPLICATE(' ',21),' '),
REPLICATE(' ', 6),' '),
REPLICATE(' ', 3),' '),
REPLICATE(' ', 2),' '),
REPLICATE(' ', 2),' ')

Except the last one, each of these numbers n may be replaced by n+1.

Edited by - Arnold Fribble on 11/12/2002 15:46:54

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources