用单个空格替换重复空格的 SQL 方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2182877/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL method to replace repeating blanks with single blanks
提问by cindi
Is there a more elegant way of doing this. I want to replace repeating blanks with single blanks....
有没有更优雅的方法来做到这一点。我想用单个空格替换重复空格....
declare @i int
set @i=0
while @i <= 20
begin
update myTable
set myTextColumn = replace(myTextColumn, ' ', ' ')
set @i=@i+1
end
(its sql server 2000 - but I would prefer generic SQL)
（它的 sql server 2000 - 但我更喜欢通用 SQL）
回答by Paul
This works:
这有效：
UPDATE myTable
SET myTextColumn =
REPLACE(
REPLACE(
REPLACE(myTextColumn
,' ',' '+CHAR(1)) -- CHAR(1) is unlikely to appear
,CHAR(1)+' ','')
,CHAR(1),'')
WHERE myTextColumn LIKE '% %'
Entirely set-based; no loops.
完全基于集合；没有循环。
So we replace any two spaces with an unusual character and a space. If we call the unusual character X, 5 spaces become: ' X X ' and 6 spaces become ' X X X'. Then we replace 'X ' with the empty string. So 5 spaces become ' ' and 6 spaces become ' X'. Then, in case there was an even number of spaces, we remove any remaining 'X's, leaving a single space.
所以我们用一个不寻常的字符和一个空格替换任意两个空格。如果我们称异常字符为 X，则 5 个空格变为：'XX'，6 个空格变为'XX X'。然后我们用空字符串替换 'X '。所以5个空格变成''，6个空格变成'X'。然后，如果有偶数个空格，我们删除所有剩余的“X”，留下一个空格。
回答by Andrew
Here is a simple set based way that will collapse multiple spaces into a single space by applying three replaces.
这是一个简单的基于集合的方法，通过应用三个替换将多个空间折叠成一个空间。
DECLARE @myTable TABLE (myTextColumn VARCHAR(50))
INSERT INTO @myTable VALUES ('0Space')
INSERT INTO @myTable VALUES (' 1 Spaces 1 Spaces. ')
INSERT INTO @myTable VALUES (' 2 Spaces 2 Spaces. ')
INSERT INTO @myTable VALUES (' 3 Spaces 3 Spaces. ')
INSERT INTO @myTable VALUES (' 4 Spaces 4 Spaces. ')
INSERT INTO @myTable VALUES (' 5 Spaces 5 Spaces. ')
INSERT INTO @myTable VALUES (' 6 Spaces 6 Spaces. ')
select replace(
replace(
replace(
LTrim(RTrim(myTextColumn)), ---Trim the field
' ',' |'), ---Mark double spaces
'| ',''), ---Delete double spaces offset by 1
'|','') ---Tidy up
AS SingleSpaceTextColumn
from @myTable
Your Update statement can now be set based:
您的 Update 语句现在可以基于以下内容进行设置：
update @myTable
set myTextColumn = replace(
replace(
replace(
LTrim(RTrim(myTextColumn)),
' ',' |'),
'| ',''),
'|','')
Use an appropriate Where clause to limit the Update to only the rows that have you need to update or maybe have double spaces.
使用适当的 Where 子句将更新限制为仅需要更新或可能有双空格的行。
Example:
例子：
where 1<=Patindex('% %', myTextColumn)
I have found an external write up on this method: REPLACE Multiple Spaces with One
我发现了一个关于这种方法的外部文章：用一个替换多个空格
回答by lynx_74
select
string = replace(
replace(
replace(' select single spaces',' ','<>')
,'><','')
,'<>',' ')
回答by gbn
SELECT 'starting...' --sets @@rowcount
WHILE @@rowcount <> 0
update myTable
set myTextColumn = replace(myTextColumn, ' ', ' ')
where myTextColumn like '% %'
回答by Lieven Keersmaekers
Not very SET Based but a simple WHILEwould do the trick.
不是非常基于 SET，但一个简单的WHILE就可以了。
CREATE TABLE #myTable (myTextColumn VARCHAR(32))
INSERT INTO #myTable VALUES ('NoSpace')
INSERT INTO #myTable VALUES ('One Space')
INSERT INTO #myTable VALUES ('Two Spaces')
INSERT INTO #myTable VALUES ('Multiple Spaces .')
WHILE EXISTS (SELECT * FROM #myTable WHERE myTextColumn LIKE '% %')
UPDATE #myTable
SET myTextColumn = REPLACE(myTextColumn, ' ', ' ')
WHERE myTextColumn LIKE '% %'
SELECT * FROM #myTable
DROP TABLE #myTable
回答by cjk
Step through the characters one by one, and maintain a record of the previous character. If the current character is a space, and the last character is a space, stuffit.
逐个遍历字符，并保留前一个字符的记录。如果当前字符是空格，而最后一个字符是空格，stuff则它。
CREATE FUNCTION [dbo].[fnRemoveExtraSpaces] (@Number AS varchar(1000))
Returns Varchar(1000)
As
Begin
Declare @n int -- Length of counter
Declare @old char(1)
Set @n = 1
--Begin Loop of field value
While @n <=Len (@Number)
BEGIN
If Substring(@Number, @n, 1) = ' ' AND @old = ' '
BEGIN
Select @Number = Stuff( @Number , @n , 1 , '' )
END
Else
BEGIN
SET @old = Substring(@Number, @n, 1)
Set @n = @n + 1
END
END
Return @number
END
GO
select [dbo].[fnRemoveExtraSpaces]('xxx xxx xxx xxx')
回答by kevchadders
create table blank(
field_blank char(100))
insert into blank values('yyy yyyy')
insert into blank values('xxxx xxxx')
insert into blank values ('xxx xxx')
insert into blank values ('zzzzzz zzzzz')
update blank
set field_blank = substring(field_blank,1,charindex(' ',field_blank)-1) + ' ' + ltrim(substring(field_blank,charindex(' ',field_blank) + 1,len(field_blank)))
where CHARINDEX (' ' , rtrim(field_blank)) > 1
select * from blank
回答by Wasim
Lets say, your Data like this
可以说，你的数据是这样的
Table name : userdata Field: id, comment, status,
id, "I love -- -- - -spaces -- - my INDIA" , "Active" <br>
id, "I love -- -- - -spaces -- - my INDIA" , "Active" <br>
id, "I love -- -- - -spaces -- - my INDIA" , "Active" <br>
id, "I love -- -- - -spaces -- - my INDIA" , "Active" <br>
So just do like this
所以就这样做
update userdata set comment=REPLACE(REPLACE(comment," ","-SPACEHERE-"),"-SPACEHERE"," ");
I didn't tested , but i think this will work.
我没有测试，但我认为这会奏效。
回答by Munavvar
Here is a Simplest solution :)
这是一个最简单的解决方案:)
update myTable
set myTextColumn = replace(replace(replace(LTrim(RTrim(myTextColumn )),' ','<>'),'><',''),'<>',' ')
回答by besartm
Try this:
尝试这个：
UPDATE Ships
SET name = REPLACE(REPLACE(REPLACE(name, ' ', ' ' + CHAR(1)), CHAR(1) + ' ', ''), CHAR(1), '')
WHERE name LIKE '% %'
