Remove extra 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

SQL Server Development (2000)

Remove extra spaces.

Author

Topic

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-13 : 17:34:49

Anyone have a quick way to pull spaces out of a variable?

Example:

DECLARE @SPACER VARCHAR(100)
SET @SPACER = ' These Darned Spaces '

I'm aware that RTRIM() and LTRIM() will knock of the leading and trailing spaces. Is there a way to get rid of all but one of the intermediate ones so that @SPACER will resolve to:

'These Darned Spaces' ?

Will it work for tabs too?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-02-13 : 17:43:29

RTRIM is what to use for the data example that you provided. If your data will be different, then use REPLACE character function. For tabs, you'll need to search for CHAR(9) and use REPLACE function to get rid of it.

Tara Kizer
aka tduggan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-14 : 00:54:39

Also, if you use Front End application to send parameter values to stored Procedure, you can do this kind of formations there and send only valid data to the procedure

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-14 : 11:11:46

This works and should handle just about anything:


SET @ONELINER = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(LTRIM(REPLACE(REPLACE(@SPACER, CHAR(13), ' '), CHAR(10), ' ')),CHAR(9),' '))), '     ', ' '),'    ', ' '), '   ', ' '), '  ', ' '), ' ', ' ')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-15 : 01:47:44

quote:

Originally posted by pug2694328

This works and should handle just about anything:


SET @ONELINER = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(LTRIM(REPLACE(REPLACE(@SPACER, CHAR(13), ' '), CHAR(10), ' ')),CHAR(9),' '))), '     ', ' '),'    ', ' '), '   ', ' '), '  ', ' '), ' ', ' ')

Did you run that successfully?

Madhivanan

Failing to plan is Planning to fail

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-15 : 10:30:39

Yup:


DECLARE @SPACER VARCHAR(100)
SET @SPACER = ' These Darned Spaces '
declare @oneliner varchar(100)

SET @ONELINER = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(REPLACE(LTRIM(REPLACE(REPLACE(@SPACER, CHAR(13), ' '), CHAR(10), ' ')),CHAR(9),' '))), '     ', ' '),'    ', ' '), '   ', ' '), '  ', ' '), ' ', ' ')

print @oneliner

Kristen
Test

22859 Posts

Posted - 2006-02-15 : 13:23:37

Three REPLACE is enough, whereas yours won't remove a huge block of spaces

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61794 (where I'm working on a generic solution)

Kristen

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-15 : 16:14:46

Thanks K, I'll keep an eye on your progress.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-15 : 19:17:48

Take a look at the solution to replacing spaces this I posted here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56195&whichpage=2

CODO ERGO SUM

pug2694328
Posting Yak Master

166 Posts

Posted - 2006-02-16 : 09:07:58

Thanks Michael, so many ways to kill a cat! : )