Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== Microsoft - Microsoft SQL Server - SQL - Format - Convert Multiple and Duplicate Spaces to Single Space ======

There are many different methods that can be used.

----

===== Using a WHILE loop with REPLACE =====

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

or

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

<WRAP info>
**NOTE:**  Recent SQL Server versions also support using the **TRIM** Function which can be used instead of **LTRIM** and **RTRIM**.
</WRAP>


----

===== Checking for Unused Characters with REPLACE =====

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

<WRAP info>
**NOTE:**  Any other character can be used instead of **^**.
</WRAP>

----

===== 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.

<code sql>
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;
</code>

<WRAP info>
**NOTE:**  This will probably be the best performing method to use.
</WRAP>