Oracle 用一个空格更新一列中的多个空格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27607353/
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
Oracle update multiple spaces in a column with a single space
提问by user1751356
I'm trying to update a column that could possibly have a single space or multiple spaces into just one single space using a plain sql statement not pl sql
我正在尝试使用普通的 sql 语句而不是 pl sql 将可能有一个空格或多个空格的列更新为一个空格
I could do it through update table set column_name='' where column_name like '% %' However, there could be some data such as abc def in that column. I do not want to disturb the pattern of that data meaning if want to do it only when the column is filled with white space and not touch columns that have any data.
我可以通过 update table set column_name='' where column_name like '% %' 来完成，但是，该列中可能有一些数据，例如 abc def。我不想打扰该数据的模式，这意味着如果只想在该列填充空白并且不接触具有任何数据的列时才这样做。
回答by David Faber
I would recommend using a regular expression to do this, both to do the replacement and to do the matching:
我建议使用正则表达式来执行此操作，既可以进行替换，也可以进行匹配：
UPDATE mytable
SET mycolumn = REGEXP_REPLACE(mycolumn, '\s{2,}', ' ')
WHERE REGEXP_LIKE(mycolumn, '\s{2,}')
This will replace two or more consecutive whitespace characters (spaces, tabs, etc.) with a single space. If you just want to replace spaces and not tabs, carriage returns, or newlines, use the following:
这将用一个空格替换两个或多个连续的空白字符（空格、制表符等）。如果您只想替换空格而不是制表符、回车符或换行符，请使用以下命令：
UPDATE mytable
SET mycolumn = REGEXP_REPLACE(mycolumn, ' {2,}', ' ')
WHERE REGEXP_LIKE(mycolumn, ' {2,}')
The reason for using {2,}is so that we don't bother replacing spaces where it need not be done.
使用的原因{2,}是为了让我们不必费心替换不需要完成的空格。
回答by Lajos Veres
With regular expression:
使用正则表达式：
update table set column=regexp_replace(column, ' +', ' ')
回答by Tony Andrews
Try:
尝试：
update mytable
set col = ' '
where replace (col, ' ', null) is null;
回答by user1751356
I have used regexp_like to solve this
我已经使用 regexp_like 来解决这个问题
update table set column= ' ' where column in (select column from table where regexp_like('column','^\s+$')
更新表集列 = '' where column in（从表中选择列 where regexp_like('column','^\s+$')
Thanks
谢谢
回答by Crozeta
Try this:
尝试这个：
update product set name = replace(name, ' ', ' ') where name like '% %';
where the second parameter of replace expression and like ('% %') contains two blank spaces.
其中 replace 表达式和 like ('% %') 的第二个参数包含两个空格。
In my case, the result was:
就我而言，结果是：
TENIS NIKE AIR => TENIS NIKE AIR
TENIS NIKE MAN => TENIS NIKE MAN
