Replace Whitespace - 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)

Replace Whitespace

Author

Topic

Nick
Posting Yak Master

155 Posts

Posted - 2003-09-23 : 10:33:11

Hello-

Is there a predefined SQL Server function that will replace whitespace. I need something that will take a string like "This is a test of the emergency broadcast system." and will return "This is a test of the emergency broadcast system." There should only be one space between words.

Would this type of function be well suited for a UDF or is there something built into SQL Server that will handle this for me?

Thanks-

Nick

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-23 : 11:50:24

How about:


DECLARE @x varchar(8000)

SELECT @x = 'This    is   a test  of the emergeny     brodacast system'

SELECT SUBSTRING(@x,1,60), REPLACE(REPLACE(REPLACE(@x,'    ',' '),'   ',' '),'  ',' ')

Should take care of up to 9 spaces I think...

And to get crazy...This should take care of up to 20 positions


DECLARE @x varchar(8000)

SELECT @x = 'This    is   a test  of the emergeny     brodacast system'

SELECT SUBSTRING(@x,1,60), REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
				@x
			,'      ',' ')
			,'     ',' ')
			,'    ',' ')
			,'   ',' ')
			,'  ',' ')

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

Nick
Posting Yak Master

155 Posts

Posted - 2003-09-23 : 11:58:48

Sorry for the original message not getting formatted properly. That will teach me to preview the post. Your code works perfectly though. Thanks!

quote:
Originally posted by X002548

How about:

DECLARE @x varchar(8000)

SELECT @x = 'This is a test of the emergeny brodacast system'

SELECT SUBSTRING(@x,1,60), REPLACE(REPLACE(REPLACE(@x,' ',' '),' ',' '),' ',' ')

Should take care of up to 9 spaces I think...

And to get crazy...This should take care of up to 20 positions

DECLARE @x varchar(8000)

SELECT @x = 'This is a test of the emergeny brodacast system'

SELECT SUBSTRING(@x,1,60), REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
@x
,' ',' ')
,' ',' ')
,' ',' ')
,' ',' ')
,' ',' ')

Brett

8-)

SELECT @@POST FROM Brain ORDER BY NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-23 : 12:33:03

nice one, Brett !

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources