SQL Server : How to remove multiple spaces from a string?

-- Detect special non-printing characters (white space) the following way

DECLARE @WhiteSpace varchar(64) = 'New York'+' '+char(9)+'City'

SELECT ExposeWP=CONVERT(varbinary(max), @WhiteSpace), NonPrint=@WhiteSpace

/* ExposeWP NonPrint

0x4E657720596F726B200943697479      New York  City  */

-- Hex 20 is space, and 09 is printing as space but it is a special character

------------

-- SQL remove unwanted spaces from text - delete extra spaces from string
DECLARE @text varchar(256)
SELECT @text = 'SQL   Server    2008   Enterprise    Edition'
SELECT len(@text)
-- 44
SELECT @text=REPLACE(@text, '  ', ' ') -- replace 2 spaces with 1 space
SELECT len(@text)
-- 38
SELECT @text=REPLACE(@text, '  ', ' ') -- replace 2 spaces with 1 space
SELECT len(@text)
-- 34
SELECT @text=REPLACE(@text, '  ', ' ') -- replace 2 spaces with 1 space
SELECT len(@text)
-- 34
SELECT @text
--SQL Server 2008 Enterprise Edition

------------

-- SQL remove extra multiple spaces in string - SQL user defined function - UDF

-- SQL scalar string function - SQL charindex - SQL replace string function

USE AdventureWorks;

GO

CREATE FUNCTION fnRemoveMultipleSpaces

               (@InputString VARCHAR(1024))

RETURNS VARCHAR(1024)

AS

  BEGIN

    WHILE CHARINDEX('  ',@InputString) > 0  -- Checking for double spaces

      SET @InputString =

        REPLACE(@InputString,'  ',' ') -- Replace 2 spaces with 1 space

    

    RETURN @InputString

  END

GO

-- Test UDF

DECLARE @Text varchar(256)

SET @Text = 'The  fox   ran into the forest   following         other   foxes!'

SELECT Original = @Text,

       SpacesRemoved = dbo.fnRemoveMultipleSpaces (@Text)

GO

 

/* Results

 

Original   

The  fox   ran into the forest   following         other   foxes!

SpacesRemoved

The fox ran into the forest following other foxes!

*/