Squeeze Function

The Squeeze function is used to remove the multiple occurences of spaces into one occurence. In SQL Server there is no function to do the same. I needed to write this in my application to remove unwanted spaces in the string.

Run the following and see the result



declare @t table(string_col varchar(100))


insert into @t

select 'a              b   c' union all 

select 'a                   b               c' union all 

select 'a       b                                        c' union all 

select 'a             b          c' union all 

select 'a   b    c' 


select 

         string_col, 

         replace(replace(replace(string_col,' ',' ~!#$^'),'~!#$^ ',''),'~!#$^','') as new_string_col 

from 
         @t

If you use front end application (VB,.NET,jave,etc), you can also simulate the same approach there

Published 06 December 2007 12:56 by Madhivanan
Filed under: ,

Comments

# re: Squeeze Function

12 December 2007 19:43 by DamianMulvena

Cool trick.

Is there a leading space missing from the special string of the outermost replace? I found that this was expanding all my spaces to double spaces the way it is here. (I tried one example where there was a single space only between the letters).

I also tested it with leading and trailing space. Although the expression squeezes this space too, it doesn't get rid of it altogether. Depending on needs people may still need to trim at each end as well.

# re: Squeeze Function

13 December 2007 06:17 by Madhivanan

Hi DamianMulvena

In order to handle leading or trailing spaces use ltrim and rtrim functions

# re: Squeeze Function

18 December 2007 22:01 by Alex Simkin

I lost all my favorite '~!#$^' comments in my blog.

# re: Squeeze Function

19 December 2007 06:17 by Madhivanan

Hi  Alex Simkin

What do you mean by that?

Do you mean that you dont understand why ~!#$^ is used?

# re: Squeeze Function

19 December 2007 11:55 by vmvadivel

Check out http://vadivel.blogspot.com/2007/01/removing-unwanted-spaces-within-string.html

# Count number of words in a string

21 December 2007 09:52 by Madhivanan

Tony Rogerson in his weblog posted on Counting the number of words in a string Here are two more methods

# Count number of words in a String

21 December 2007 10:10 by SQL Server Transact-SQL (SSQA.net)

Tony Rogerson in his weblog posted about Counting the number of words in a string Here are two more methods

# re: Squeeze Function

24 December 2007 14:34 by Mark Kane

Is your code more efficient than using a loop to process the input? What if the string is very long and has lots of whitespace?

This an example of using a loop to squeeze out extra white space.

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

# re: Squeeze Function

24 December 2007 14:52 by Madhivanan

Hi Mark Kane

Why dont you test and post back the result? Smile

# re: Squeeze Function

31 December 2007 11:45 by Sandy Sinclair

Hi Madhivanan,

could you explain why ~!#$^ is used for me please?

Many thanks in advance

# re: Squeeze Function

02 January 2008 06:43 by Madhivanan

Hi Sandy Sinclair

I am replacing double spaces with sometext which sould not be part of the string. So I used ~!#$^

# re: Squeeze Function

12 February 2008 17:26 by Luke

Nice thing but what if I want to leave only one space in the string instead of deleting all of them?

# re: Squeeze Function

12 February 2008 20:08 by Vadivel

Luke,

May be you are looking for this http://vadivel.blogspot.com/2007/01/removing-unwanted-spaces-within-string.html

# re: Squeeze Function

13 February 2008 09:03 by Madhivanan

Luke,

The example provided does the thing you want. Isnt it?

# re: Squeeze Function

13 February 2008 09:04 by Madhivanan

Vadivel,

Your code hangs

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

Select @strValue