OK - this might be stretching the "Is this Useful" question a bit, but hey, I needed it today and thought I would share it with you - it's free! This construct takes the whitespace out of a string.
Removes extra white spaces between two words in a character string
PingBack from http://msdnrss.thecoderblogs.com/2007/08/24/script-of-the-day-remove-white-space-from-a-string/
the example only works for the first space in a string. If there are lots of words and lots of spaces try this below.
CREATE FUNCTION [dbo].[Squeeze]
declare @outtext varchar(max),
select @count = 0,
@inpos = 0,
@outtext = '',
@outpos = 0,
@prevchar = ' '
set @inpos = len(@intext)
while (@count <= @inpos)
set @char = substring(@intext,@count,1)
if (@prevchar<>' ' OR @char<>' ')
set @outtext = @outtext + @char
set @outpos = @outpos + 1
set @prevchar = @char
set @count = @count + 1
I was searching for days to get this code.
It works for 'Buck Woody'
But does not work for 'Buck abc Woody'
Thanks, bobthecoder. Works great.
In SQLServer we can use the built in REPLACE function to replace all whitespaces in the given text.
select REPLACE('input string',' ','')
A nice clever solution that works with strings with more than 2 words is given here: www.itjungle.com/.../fhg101106-story02.html
A really useful thing would be a function to remove all multiple whitespace from a string. It's a shame that this is impossible in sql server, I miss the programming environment of the 1980s where you would have rich functionality. Heck, a VIC-20 had better string capabilities than microsoft put in t-sql.
Oh wel - nice Troll! Not gonna bite however. Lots of ways to do this in T-SQL. Keep reading - you'll figure it out!
Woody - you have no idea what gold this is to me right at the moment: bloody invaluable!!
Why not just something like this?!
select Replace(Replace(Replace('the cat sat on the mat',' ',' |'),'| ',''),'|','')