Wednesday, January 17, 2007

Removing unwanted spaces within a string ...

Removing leading and trailling spaces is pretty easy. All you need to do is make use of Ltrim and Rtrim function respectively. But there are times when you want to remove unwanted spaces within a string. Check out the below code snippet to know how to do it.

--Declaration and Initialization
Declare @strValue varchar(50)
Set @strValue = ' I Love you ! ' -- Here between each word leave as many spaces as you want.

--Remove the leading and trailing spaces
Set @strValue = Rtrim(Ltrim(@strValue))

--Loop through and remove more than one spaces to single space.
While CharIndex(' ',@strValue)>0
Select @strValue = Replace(@strValue, ' ', ' ')

--Final output :)
Select @strValue

5 comments:

Saravanan said...

Following code has to be changed
Select @strValue = Replace(@strValue, ' ', ' ')

to

Select @strValue = Replace(@strValue, ' ', '')

otherwise, it will generate a infinity loop.

Madhivanan said...

Without loop, you can do this

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/06/squeeze-function.aspx

http://sqlblogcasts.com/blogs/madhivanan

PowR_TocH said...

One thing bugs me - how to modify this code to leave only one instance of the word/character?
While CharIndex(' ',@strValue) = 1

does not work.

Madhivanan said...

Vadivel,

You didnt modify your code as Saravanan suggested. Did you?

Madhivanan said...

Vadivel,

You didnt modify your code as Saravanan suggested. Did you?