Script of the day – Remove White Space from a String


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.


/*


usc_DBA_Remove_White_Space.sql


Removes extra white spaces between two words in a character string


*/


DECLARE @TEXT VARCHAR(100)


DECLARE @I INT


SET @TEXT = ‘Buck                                                 Woody’


SET @I = CHARINDEX (‘ ‘ , @TEXT )


PRINT SUBSTRING(@TEXT,1,@i1) + ‘ ‘ +


LTRIM(SUBSTRING(@TEXT,@I,len(@TEXT)))

Comments (12)

  1. bobthecoder says:

    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

  2. Prasad says:

    Perfeect one.

    I was searching for days to get this code.

    Thanks

  3. Janhavi says:

    It works for 'Buck                                                 Woody'

    But does not work for 'Buck     abc                         Woody'

  4. Chris says:

    Thanks, bobthecoder. Works great.

  5. Sandhya Shenoy says:

    In SQLServer we can use the built in REPLACE function  to replace all whitespaces in the given text.

    select REPLACE('input string',' ','')

  6. P.Petkov says:

    A nice clever solution that works with strings with more than 2 words is given here: http://www.itjungle.com/…/fhg101106-story02.html

  7. Oh wel says:

    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.

  8. BuckWoody says:

    Oh wel – nice Troll! Not gonna bite however. Lots of ways to do this in T-SQL. Keep reading – you'll figure it out!

  9. Tristan Rentz says:

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

    (muchos grazias)

  10. Abu Dina says:

    Why not just something like this?!

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