删除/替换列值中的特殊字符? [英] Remove/replace special characters in column values?
问题描述
我有一个表列,其中包含我想从中删除所有连字符的值。这些值可能包含多个连字符,并且长度不同。
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.
示例:对于所有值,我都想用 123ABCDefghi代替 123-ABCD-efghi。
Example: for all values I would like to replace '123 - ABCD - efghi' with '123ABCDefghi'.
删除所有连字符&的最简单方法是什么?更新表中的所有列值?
What is the easiest way to remove all hyphens & update all column values in the table?
推荐答案
您可以使用 regexp_replace
仅保留数字和字母的功能,如下所示:
You can use the regexp_replace
function to left only the digits and letters, like this:
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).
如果您还希望包含要替换的 _
( \w
将保留它),您可以将正则表达式更改为 [^ \w] + | _
。
If you want to also include the _
to be replaced (\w
will leave it) you can change the regex to [^\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.
这篇关于删除/替换列值中的特殊字符?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!