To Remove Multiple and Duplicate Spaces to Single Space in SQL Server

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;

WHILE Loop and REPLACE Method Without TRIM

• 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;

WHILE Loop and REPLACE Method With LTRIM and RTRIM

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;

Unused Characters in REPLACE Method with LTRIM and RTRIM

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

Non-Printable ASCII Characters

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; 

ASCII Non-Printable Characters in REPLACE Method with LTRIM and RTRIM

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.

Thanks for reading!

Advertisement