MS SQL 2005, replace multi space to 1 space.

The MS SQL server 2005 stores some data: "this is        a                   test              for  data"
and now I want to convert them to: "this is a test for data"

How can I do it?
JameMeckAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
apresenceConnect With a Mentor Commented:
Take a look at:
http://www.sqlhacks.com/Retrieve/Remove-Blanks

For example:
select ltrim(replace(replace(replace('this                      is         a       test', '  ', ' `````'), '````` ',''),'`````',''))

Returns:
this is a test
0
 
Mohit VijayCommented:
use REPLACE(YourString, '  ',' ')

0
 
apresenceCommented:
VjSoft:
The replace is not recursive.  Using your example:
replace('this                      is         a       test', '  ', ' ')

Produces this output:
this           is     a    test
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mohit VijayCommented:
@apresence, I only provide hint, if you need code example here it is

SELECT REPLACE(REPLACE('this                      is         a       test','  ','-'),'-','')
0
 
apresenceCommented:
Query:
SELECT REPLACE(REPLACE('this                      is         a       test','  ','-'),'-','')

Output:
thisis a test
0
 
cetafuddCommented:
I found this a while ago and am using it -- it works great

DECLARE @OriginalString VARCHAR(100)

SET @OriginalString = 'this is        a                   test              for  data'


SELECT
REPLACE(          
      REPLACE(
            REPLACE(
                  LTRIM(RTRIM(@OriginalString))
                        ,'  ',' '+CHAR(7))  
                  ,CHAR(7)+' ','')        
            ,CHAR(7),'') AS CleanString
-- FROM yourtable (instead of using @originalString variable)
WHERE CHARINDEX('  ',@OriginalString) > 0
0
 
Lara FEACommented:
This one trims all type of "spaces", not just  plain char(32)


create FUNCTION fVal_trimSpaces
      (@name varchar(8000)
        )
RETURNS varchar(8000)
AS
BEGIN
      select @name = replace(replace(replace(replace(replace(replace(replace(replace
            (@name,'      ' ,' ')
                  ,char(160),' ')
                  ,char(32),' ')
                  ,char(9),' ')
                  ,char(10),' ')
                  ,char(11),' ')
                  ,char(12),' ')
                  ,char(13),' ')
       while (charindex('  ', @name) > 0)
            select  @name =  replace(@name,'  ', ' ')

      select @name = rtrim(ltrim(@name))
      return @name

END
0
 
Vipul Patel.NET ExpertCommented:
I would prefer to write CLR function for better performance.
Please visit http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
0
 
JameMeckAuthor Commented:
Thanks! It is my solution.
0
All Courses

From novice to tech pro — start learning today.