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 :)