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
--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
Comments
Select @strValue = Replace(@strValue, ' ', ' ')
to
Select @strValue = Replace(@strValue, ' ', '')
otherwise, it will generate a infinity loop.
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/12/06/squeeze-function.aspx
http://sqlblogcasts.com/blogs/madhivanan
While CharIndex(' ',@strValue) = 1
does not work.
You didnt modify your code as Saravanan suggested. Did you?
You didnt modify your code as Saravanan suggested. Did you?