This is an old revision of the document!

Microsoft - Microsoft SQL Server - SQL - Format - Convert Multiple and Duplicate Spaces to Single Space

Using a WHILE loop with REPLACE

SQL QUERY

DECLARE @TestString VARCHAR(200);
SET @TestString='   Ex      ample   St  ring   ';
WHILE CHARINDEX('  ',@TestString) <> 0  SET @TestString = REPLACE(@TestString,'  ',' ');
SELECT @TestString AS RESULT;

or

DECLARE @TestString VARCHAR(200);
SET @TestString='   Ex      ample   St  ring   ';
WHILE CHARINDEX('  ',@TestString) <> 0  SET @TestString = REPLACE(@TestString,'  ',' ');
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;

NOTE: Recent SQL Server versions also support using the TRIM Function which can be used instead of LTRIM and RTRIM.

Checking for Unused Characters with REPLACE

DECLARE @TestString VARCHAR(200);
SET @TestString='   Ex      ample   St  ring   ';
SELECT @TestString = REPLACE(     REPLACE(         REPLACE(@TestString, ' ', ' ^')     ,'^ ', '') , '^', '');
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;

NOTE: Any other character can be used instead of ^.

Checking ASCII Non-Printable Characters with REPLACE

If unsure on what unused characters to use, rather use ASCII Non-Printable characters instead of Unused characters.

DECLARE @TestString VARCHAR(200);
SET @TestString='   Ex      ample   St  ring   ';
SELECT @TestString = REPLACE(     REPLACE(             REPLACE(@TestString,' ',CHAR(17)+CHAR(18))     ,CHAR(18)+CHAR(17),'') ,CHAR(17)+CHAR(18),' ');
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;

Wiki

Table of Contents

Microsoft - Microsoft SQL Server - SQL - Format - Convert Multiple and Duplicate Spaces to Single Space

Using a WHILE loop with REPLACE

Checking for Unused Characters with REPLACE

Checking ASCII Non-Printable Characters with REPLACE