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

转载 2015年02月13日 11:22:54

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.

Pleasehave a look at some of the other aricles I have written covering 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

 

 

相关文章推荐

How to Upload Multiple files in one request along with other string parameters in android?

Hello everyone, I have shown two methods to upload files in android. In today’s tutorial I will sh...
  • Koupoo
  • Koupoo
  • 2012年04月16日 16:26
  • 890

SQL Server: Add column with default value and description in T-SQL

假设我要在表 dbo.tblBinaryCommand 中添加一个新字段 ProtocolId,并且在添加字段的时候指定数据类型为 bigint,不允许为空,默认值为 -1,我还想给这个字段加一段De...

Effects with the Pixel Bender Toolkit – Part 4: Sampling multiple pixels

Requirements User level BeginningRequired products Flash Player 10 and later Flash Professional (Dow...

Bash String Manipulation Examples – Length, Substring, Find and Replace

by Sasikala on July 23, 2010  source link:http://www.thegeekstuff.com/2010/07/bash-string-manipula...

jQuery Autocomplete with multiple fileds in Asp.Net C#

 This  article explains jQuery autocomplete with  multiple fields using Asp.Net C#.Scenario :  Listi...

Getting all selected rows in ADF Table with multiple rows selection enabled

Getting all selected rows in ADF Table with multiple rows selection enabled When we build a web a...

Bash String Manipulation Examples – Length, Substring, Find and Replace

In bash shell, when you use a dollar sign followed by a variable name, shell expands the variable wi...
  • macyang
  • macyang
  • 2012年05月10日 15:36
  • 3022

文献阅读笔记: Real-time Multiple Objects Tracking with Occlusion Handling in Dynamic Scenes ---by 香蕉麦乐迪

文献阅读笔记: Real-time Multiple Objects Tracking with Occlusion Handling in Dynamic Scenes ---by 香蕉麦乐迪 文章...

Spring JTA multiple resource transactions in Tomcat with Atomikos example

In this tutorial we shall show you how to implement JTA multiple resource transactions in a Tomcat s...
  • gmemai
  • gmemai
  • 2015年09月04日 20:53
  • 203
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:T-SQL – Replace Multiple ExtraWhitespaces in a String with One Whitespace
举报原因:
原因补充:

(最多只允许输入30个字)