Script of the day - Remove White Space from a String

PingBack from http://msdnrss.thecoderblogs.com/2007/08/24/script-of-the-day-remove-white-space-from-a-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]

(

@intext varchar(max)

)

returns varchar(max)

AS

BEGIN

declare @outtext  varchar(max),

      @char  char(1),

      @inpos   int,

      @count int,

      @outpos int,

      @prevchar char(1)

select  @count = 0,

      @inpos   = 0,

      @outtext  = '',

      @outpos = 0,

      @prevchar = ' '

set @inpos = len(@intext)

while (@count <= @inpos)

begin

set @char = substring(@intext,@count,1)

if (@prevchar<>' ' OR @char<>' ')

  begin

    set @outtext = @outtext + @char

    set @outpos =  @outpos + 1

    set @prevchar = @char

  end

set @count = @count + 1

end

return  @outtext

END

GO

Perfeect one.

I was searching for days to get this code.

Thanks

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.

Woody - you have no idea what gold this is to me right at the moment: bloody invaluable!!

(muchos grazias)

Why not just something like this?!

select Replace(Replace(Replace('the     cat sat   on        the                      mat',' ',' |'),'| ',''),'|','')