postgresql 删除/替换列值中的特殊字符？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44191167/
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
Remove/replace special characters in column values?
提问by Don P
I have a table column containing values which I would like to remove all the hyphens from. The values may contain more than one hyphen and vary in length.
我有一个包含值的表列，我想从中删除所有连字符。这些值可能包含多个连字符并且长度不同。
Example: for all values I would like to replace '123 - ABCD - efghi' with '123ABCDefghi'.
示例：对于所有值，我想将 '123 - ABCD - efghi' 替换为 '123ABCDefghi'。
What is the easiest way to remove all hyphens & update all column values in the table?
删除所有连字符并更新表中所有列值的最简单方法是什么？
回答by Jorge Campos
You can use the regexp_replacefunction to left only the digits and letters, like this:
您可以使用该regexp_replace函数只留下数字和字母，如下所示：
update mytable
set myfield = regexp_replace(myfield, '[^\w]+','');
Which means that everything that is not a digit or a letter or an underline will be replaced by nothing (that includes -, space, dot, comma, etc).
这意味着所有不是数字、字母或下划线的东西都将被什么都不替换（包括-, space, dot, comma等）。
If you want to also include the _to be replaced (\wwill leave it) you can change the regex to [^\w]+|_.
如果您还想包含_要替换的（\w将保留它），您可以将正则表达式更改为[^\w]+|_.
Or if you want to be strict with the characters that must be removed you use: [- ]+in this case here a dash and a space.
或者，如果您想严格处理必须删除的字符，请使用：[- ]+在这种情况下，这里是一个破折号和一个空格。
回答by kimdasuncion12
You can use this.
你可以用这个。
update table
set column = format('%s%s', left(column, 3), right(column, -6));
update table
set column = format('%s%s', left(column, 3), right(column, -6));
Before:
前：
After:
后：
