SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Removing spaces
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

hendrasd
Starting Member

Indonesia
20 Posts

Posted - 11/11/2002 :  22:30:46  Show Profile  Send hendrasd a Yahoo! Message  Reply with Quote
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

USA
547 Posts

Posted - 11/11/2002 :  22:53:49  Show Profile  Reply with Quote
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
Go to Top of Page

jsarraf
Starting Member

Australia
2 Posts

Posted - 11/11/2002 :  23:51:34  Show Profile  Reply with Quote
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
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/12/2002 :  04:58:20  Show Profile  Reply with Quote
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,' ',' '),' ',' '),' ',' '),
' ',' '),' ',' '),' ',' '),' ',' '),' ',' '),
' ',' '),' ',' '),' ',' '),' ',' '),' ',' ')



Go to Top of Page

Andraax
Aged Yak Warrior

Sweden
790 Posts

Posted - 11/12/2002 :  05:03:44  Show Profile  Reply with Quote
Nice Arnold.... Never would have thought of that (duh...)

Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/12/2002 :  05:52:42  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000