MS SQLServer

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

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

Method #1

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

Here is a code examplethat 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 elegantas the first, but also gets the job done. This method works by nesting four (oroptionally more) replace statements that replace two blank spaces with oneblank 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 spacesbetween words is to use a simple loop. You can do a check on extra spaces in awhile loop and then use the replace function to reduce the extra spaces witheach 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 betweeneach of the three methods was a matter of milliseconds. However with largeroperations the variance will naturally compound so it is a good idea to choosea method that will be able to deal with the data volumes you expect.

In terms of efficiency for total executiontime, in both tests of ten the simple loop technique in Method #3 wasthe most efficient method of removing the extra spaces. This issomewhat surprising since one would expect a loop to perform slower than theoptimized 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 firsttest.

Here is the full test code in one placefor 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

http://jwcooney.com/2013/05/04/t-sql-replace-multiple-extra-whitespaces-in-a-string-with-one-whitespace/

another link:

http://stackoverflow.com/questions/2455750/replace-duplicate-spaces-with-a-single-space-in-t-sql

• 本文已收录于以下专栏：

举报原因： 您举报文章：T-SQL – Replace Multiple ExtraWhitespaces in a String with One Whitespace 色情 政治 抄袭 广告 招聘 骂人 其他 (最多只允许输入30个字)