Link to home
Start Free TrialLog in
Avatar of JameMeck
JameMeckFlag for United States of America

asked on

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?
ASKER CERTIFIED SOLUTION
Avatar of apresence
apresence

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
use REPLACE(YourString, '  ',' ')

Avatar of apresence
apresence

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

Produces this output:
this           is     a    test
@apresence, I only provide hint, if you need code example here it is

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

Output:
thisis a test
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
Avatar of Lara F
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
I would prefer to write CLR function for better performance.
Please visit http://msdn.microsoft.com/en-us/magazine/cc163473.aspx
Avatar of JameMeck

ASKER

Thanks! It is my solution.