Script of the day - Remove White Space from a String

Script of the day - Remove White Space from a String

  • Comments 12

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,@i-1) + ' ' +

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

Leave a Comment
  • Please add 3 and 3 and type the answer here:
  • Post
  • 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.

  • 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!!

    (muchos grazias)

  • Why not just something like this?!

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

Page 1 of 1 (12 items)