T-SQL – Replace Multiple Extra Whitespaces in a String with One Whitespace

In this article I will review three easy methods that can be used to replace extra spaces between words in a string. Note that if you want to remove extra spaces from the start and end of your string you will still need to encapsulate the output of the routines within the standard LTrim / RTrim functions.

Each of these methods works quite nicely and can either be written directly into your code, or can be added as a user-defined function. I have tested these functions in SQL Server 2000 and SQL Server 2008 and they work very well in either of these versions of SQL Server.

Please have a look at some of the other aricles I have written covering SQL Server.

Method #1

The first method is to replace extra spaces between words with an uncommon symbol combination as a temporary marker. Then you can replace the temporary marker symbols using the replace function rather than a loop.

Here is a code example that replaces text within a String variable.

DECLARE @testString AS VARCHAR(256) = ' Test        text   with  random*        spacing. Please normalize  this spacing!';
SELECT REPLACE(REPLACE(REPLACE(@testString, ' ', '*^'), '^*', ''), '*^', ' ');

1. Execution Time Test #1: In ten runs of this replacement method, the average wait time on server replies was 1.7 milliseconds and total execution time was 4.6 milliseconds.
2. Execution Time Test #2: The average wait time on server replies was 1.7 milliseconds and total execution time was 3.7 milliseconds.

Method #2

The second method is not quite as elegant as the first, but also gets the job done. This method works by nesting four (or optionally more) replace statements that replace two blank spaces with one blank space.

DECLARE @testString AS VARCHAR(256) = ' Test        text   with  random*        spacing. Please normalize  this spacing!';
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@testString,' ',' '),' ',' '),' ',' '),' ',' ')

1. Execution Time Test #1: In ten runs of this replacement method, the average wait time on server replies was 1.9 milliseconds and total execution time was 3.8 milliseconds.
2. Execution Time Test #2: The average wait time on server replies was 1.8 milliseconds and total execution time was 4.8 milliseconds.

Method #3

The third method of replacing extra spaces between words is to use a simple loop. You can do a check on extra spaces in a while loop and then use the replace function to reduce the extra spaces with each iteration of the loop.

DECLARE @testString AS VARCHAR(256) = ' Test text with random* spacing. Please normalize this spacing!';
WHILE CHARINDEX(' ',@testString) > 0
SET @testString = REPLACE(@testString, ' ', ' ')
SELECT @testString

1. Execution Time Test #1: In ten runs of this replacement method, the average wait time on server replies was 1.8 milliseconds and total execution time was 3.4 milliseconds.
2. Execution Time Test #2: The average wait time on server replies was 1.9 milliseconds and total execution time was 2.8 milliseconds.

Conclusion

In terms of speed, the variance between each of the three methods was a matter of milliseconds. However with larger operations the variance will naturally compound so it is a good idea to choose a method that will be able to deal with the data volumes you expect.

In terms of efficiency for total execution time, in both tests of ten the simple loop technique in Method #3 was the most efficient method of removing the extra spaces. This is somewhat surprising since one would expect a loop to perform slower than the optimized code in the replace statements.

Second place was a tie between the other two methods with Method #1 being faster in the second test, and Method #2 being faster in the first test.

Here is the full test code in one place for you to run in SQL Server Management Studio (SSMS):

DECLARE @testString AS VARCHAR(256) = ' Test        text   with  random*        spacing. Please normalize  this spacing!';
 SELECT REPLACE(REPLACE(REPLACE(@testString, ' ', '*^'), '^*', ''), '*^', ' ');
SELECT REPLACE(REPLACE(REPLACE(REPLACE(@testString,' ',' '),' ',' '),' ',' '),' ',' ')
DECLARE @modifyString AS VARCHAR(256)
 SET @modifyString = @testString
WHILE CHARINDEX(' ',@modifyString) > 0
 SET @modifyString = REPLACE(@modifyString, ' ', ' ')
SELECT @modifyString