How can i replace some set of white space to one white space in between string

Dear Frnd

I have to remove some set of white space in between the string and i have to replace with one white space

eg:- my name is divakar

I have to change the string to like this

my name is divakar.

so how can write sql query for that

Code Snippet

declare @.var varchar(100)

set @.var = 'my name is divakar'

while charindex(' ', @.var) > 0 --two spaces

begin

set @.var = replace(@.var, ' ', ' ') -- first is two spaces, second is one space

end

select @.var

|||

Having a function (recursive) to use it on your query...

Code Snippet

Create Function dbo.ProperWhiteSpace

(

@.String varchar(1000)

)

Returns Varchar(1000)

as

Begin

If CharIndex('',@.String) <> 0

Return dbo.ProperWhiteSpace(Replace(@.String,'', ' '));

Return @.String

End

Go

Select dbo.ProperWhiteSpace('mynameis divakar')

|||

Thx Dale

|||thx Manivannan.D.Sekaran