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

 

 

String length must be a multiple of four.

今天在整理2013年的工作时的一个项目,修改了数据库连接,初始化数据库,部署运行报错,主要原因是阿里巴巴druid报错,导致DataSource初始化失败。 druid报错日志: Caused by:...
  • zengmingen
  • zengmingen
  • 2017-12-18 14:38:56
  • 1207

PyCharm选择性忽略PEP8代码风格警告信息

用了几天的PyCharm,发现确实在编写Python代码上非常好用,但有一点体验不太好,就是代码编写时要按照PEP8代码风格编写,不然会有波浪线的警告信息。解决方法如下: 方法一: 将鼠标移到...
  • zgljl2012
  • zgljl2012
  • 2018-02-08 10:00:50
  • 47692

字符编码之Base64

博学,切问,近思--詹子知(http://blog.csdn.net/zhiqiangzhan) 1.什么是Base64.Base64是网络上最常见的用于传输8Bit字节代码的编码方式之一,大家可以查...
  • zhiqiangzhan
  • zhiqiangzhan
  • 2009-10-12 11:28:00
  • 6621

BASE64Encoder

package com.avetti.util;/*** A Base64 Encoder/Decoder.** * This class is used to encode and decode d...
  • lycgxy
  • lycgxy
  • 2009-08-06 09:39:00
  • 3033

OCJP标准题库试题之————第二次

QUESTION 13 Click the Exhibit button. Which statement is true about the classes and interfaces in th...
  • u013410747
  • u013410747
  • 2015-12-10 16:50:02
  • 1108

Nginx:配置指南(1)

This guide gives a basic introduction to nginx and describes somesimple tasks that can be done with ...
  • zhoudaxia
  • zhoudaxia
  • 2014-07-20 22:29:46
  • 3970

iOS:NSString 使用杂记

********************拆分Separated******************** - (NSArray *)componentsSeparatedByString:(NSStr...
  • houseq
  • houseq
  • 2014-08-07 09:13:37
  • 1711

Linux C++编译报错:"multiple definition of" / "does not name a type"

最近编译larbin_daemon爬虫服务器端管理程序时,总是会报如下的错误: g++ -c -o editConf.o editConf.cpp editConf.cpp:49:8: erro...
  • dongfengkuayue
  • dongfengkuayue
  • 2015-01-06 19:15:43
  • 2084

(4) 如何用Apache POI操作Excel文件-----发现了POI-3.12一个回归bug

好不容易盼望POI-3.12的最终版本能出来,这个月终于出来,于是先堵为快,从这个的地址(https://poi.apache.org/download.html#POI-3.12)下载最新的POI-...
  • chancein007
  • chancein007
  • 2015-05-29 17:16:32
  • 2893

B?Cryptoquote

B ? Cryptoquote 时间限制(普通/Java) : 1000 MS/ 3000 MS          运行内存限制 : 65536 KByte 总提交 : 42         ...
  • wyh7280
  • wyh7280
  • 2015-03-25 16:42:03
  • 327
收藏助手
不良信息举报
您举报文章:T-SQL – Replace Multiple ExtraWhitespaces in a String with One Whitespace
举报原因:
原因补充:

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