Alternatives · Basic Logics · Real Scenario Logic's · String Functions · String Manipulation

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!

5 thoughts on “To Remove Multiple and Duplicate Spaces to Single Space in SQL Server

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

    Liked by 1 person

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s