METHOD 1 - WHILE LOOP AND REPLACE
By Using WHILE Loop, we can check the occurrences of double spaces, as Loop Condition.
Till the occurrence (Loop) fails, we need to alter every Double Spaces as Single Space.
DECLARE @TestString VARCHAR(200);
SET @TestString=' Ex ample St ring ';
WHILE CHARINDEX(' ',@TestString) <> 0
SET @TestString = REPLACE(@TestString,' ',' ');
SELECT @TestString AS RESULT;
If we also want to remove spaces at front and end of string, then use LTRIM AND RTRIM Functions.
If our SQL Server version is higher, then we can also use TRIM Function instead of LTRIM and RTRIM.
DECLARE @TestString VARCHAR(200);
SET @TestString=' Ex ample St ring ';
WHILE CHARINDEX(' ',@TestString) <> 0
SET @TestString = REPLACE(@TestString,' ',' ');
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;
METHOD 2 - UNUSED CHARACTERS IN REPLACE
DECLARE @TestString VARCHAR(200);
SET @TestString=' Ex ample St ring ';
SELECT @TestString =
REPLACE(
REPLACE(
REPLACE(@TestString, ' ', ' ^')
,'^ ', '')
, '^', '');
SELECT LTRIM(RTRIM(@TestString)) AS RESULT;
We can also use any other character too instead of ‘^’.
Main thing is, it should not be one of the characters that is already present in input string.
METHOD 3 - ASCII NON-PRINTABLE CHARACTERS IN REPLACE
If we are not sure on what unused characters to use, then suggestion is to use ASCII Non-Printable characters instead of Unused characters like below.
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;
CONCLUSION
We can use the above methods to Remove Duplicate as well as Multiple spaces to Single space. If we consider performance, Method 3 will be better than other two methods.