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 Thursday, December 6, 2007 12:56 PM by Madhivanan
Filed under: ,

Comments

# re: Squeeze Function

Wednesday, December 12, 2007 7:43 PM 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

Thursday, December 13, 2007 6:17 AM by Madhivanan

Hi DamianMulvena

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

# re: Squeeze Function

Tuesday, December 18, 2007 10:01 PM by Alex Simkin

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

# re: Squeeze Function

Wednesday, December 19, 2007 6:17 AM by Madhivanan

Hi  Alex Simkin

What do you mean by that?

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

# re: Squeeze Function

Wednesday, December 19, 2007 11:55 AM by vmvadivel

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

# Count number of words in a string

Friday, December 21, 2007 9:52 AM 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

Friday, December 21, 2007 10:10 AM 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

Monday, December 24, 2007 2:34 PM 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

Monday, December 24, 2007 2:52 PM by Madhivanan

Hi Mark Kane

Why dont you test and post back the result? Smile

# re: Squeeze Function

Monday, December 31, 2007 11:45 AM by Sandy Sinclair

Hi Madhivanan,

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

Many thanks in advance