
In this blog post, we would see some of the easy methods to remove Multiple as well as Duplicate Spaces to Single Space using T-SQL in SQL Server like below.
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.
SQL QUERY
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.
SQL QUERY
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
SQL QUERY
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.

SQL QUERY
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.
Nice one. Removing multiple white spaces into single is done by SQUEZZE functions in few programming languages
LikeLiked by 1 person
New to know Sir!
LikeLike
Hey folks… you need to go back an read the prologue of my original article on the subject. Right after that article came out, a bunch of good people got together on the discussion for the article and found a much better way than doing the “unused character replacement” I was using and certainly much better than using any form of WHILE loop. I have links in the prologue to the posts in the discussion that have the much better method.
LikeLiked by 1 person