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字符串的replace函数和正则表达式

package zhengze; public class sss { public static void main(String[] args) { // TODO A...
  • anlian523
  • anlian523
  • 2017年04月28日 19:28
  • 1461

String 对象中的 正则表达式方法 replace

定义和用法 replace() 方法用于在字符串中用一些字符替换另一些字符,或替换一个与正则表达式匹配的子串。 语法 stringObject.replace(regexp/substr...
  • lt3487928
  • lt3487928
  • 2016年11月14日 10:12
  • 1036

C++ STL replace()函数常用用法详解

replace算法:                 replace函数包含于头文件#include中。               泛型算法replace把队列中与给定值相等的所有值替换为另一个...
  • zwj1452267376
  • zwj1452267376
  • 2015年07月09日 20:49
  • 9279

Java 中replaceAll忽略大小写

原文:http://hw1287789687.iteye.com/blog/2150897 Java 中replaceAll如何忽略大小写呢? 方式一:在正则表达式前面添加(?i)...
  • u012109105
  • u012109105
  • 2015年07月22日 15:19
  • 1500

string.replace()替换匹配给定的正则表达式的(一个或多个)子串

在百度前端学习js遇到的问题,总结一下: 概要:string.relace(regexp,replacement)参数 regexp:指定要替换的模式RegExp对象。如果这个参数是一个字符串,他...
  • qq_34149935
  • qq_34149935
  • 2017年03月27日 21:25
  • 573

C++ replace() 函数用法详解

本文主要针对c++中常用replace函数用法给出样例程序 [cpp] view plain copy /*用法一:   *用str替换指定字符串...
  • jiary5201314
  • jiary5201314
  • 2016年09月11日 10:05
  • 2126

在使用 Spring Boot 和 MyBatis 动态切换数据源时遇到的问题以及解决方法

相关项目地址:https://github.com/helloworlde/SpringBoot-DynamicDataSource/tree/dev 1. org.apache.ibatis.bin...
  • u013360850
  • u013360850
  • 2017年12月22日 00:40
  • 416

String的一部分替换为星号“*”

原文章 :http://blog.csdn.net/watermusicyes/article/details/50821282 项目中有一些地方需要做到保密的效果: 用户名一部分需要替换为...
  • lishk314
  • lishk314
  • 2016年09月20日 11:04
  • 806

字符串中符号的替换---replace的用法

字符串中符号的替换---replace的用法 1 #include 2 #includestring> 3 4 using namespace std...
  • liushuiwen101423
  • liushuiwen101423
  • 2014年09月25日 11:33
  • 343

Java多线程和String知识点< 2 >

距离上次Java的学习有点儿远,工作中还要做自己的公司的项目,所以耽误了,但是,会持续学习,更新自己的笔记。 一、多线程 1.多线程安全问题 问题原因:     当多条语句在操作同一个线程共享数...
  • BelieveLIUZ
  • BelieveLIUZ
  • 2016年12月15日 14:14
  • 481
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:T-SQL – Replace Multiple ExtraWhitespaces in a String with One Whitespace
举报原因:
原因补充:

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