替换正则表达式在PostgreSQL中不起作用 [英] replace regex does not work in postgresql
问题描述
我有一个带有一列字符串的表。字符串中有一个单引号,我想摆脱所有的单引号。例如:
I have a table with a column of string. within the string there are single quote which I want to get rid of all single quotes.for example:
"''hey, hey, we're the monkees''"
我的正则表达式非常完善,并选择了所有包含单个值的值
my regex works perfect and select all the values containing single quotes.
select regexp_replace(colName, '%''%', '') from tblName;
,但是当我不想用任何正则表达式替换它时,它不会更新我的表。
but it does not update my table when I want to replace this regex with nothing.
UPDATE tblName SET colName = regexp_replace(colName, '%''%', '');
我也检查了这个
UPDATE tblName SET colName = replace(colName, '%''%', '');
推荐答案
Postgres中的不同函数和运算符使用以下三种之一模式匹配语言,如手册专用部分中所述的 。
Different functions and operators in Postgres use one of three different pattern matching languages, as described in a dedicated section of the manual.
您在此处使用的%
表单是SQL Like
语法,其中%
表示任意数量的任何字符。但是您使用的函数 regexp_replace
期望使用Posix正则表达式,其中等价于。*
(。
表示任何字符, *
表示重复零次或多次)。
The %
form you are using here is the SQL LIKE
syntax, where %
represents "any number of any character". But the function you are using, regexp_replace
, expects a Posix regular expression, where the equivalent would be .*
(.
meaning any character, *
meaning repeat zero or more times).
还要注意, Like
表达式必须匹配整个字符串,但是Posix regex不需要匹配,除非您明确地将字符串的开头与 ^
匹配,并将结尾与 $
匹配。
Also note that LIKE
expressions have to match the whole string, but a Posix regex doesn't, unless you explicitly match the start of the string with ^
and the end with $
.
因此'%''%'
的直接翻译为'^。*''。* $'
,为您提供:
So the direct translation of '%''%'
would be '^.*''.*$'
, giving you this:
UPDATE tblName SET colName = regexp_replace(colName, '^.*''.*$', '');
实际上,这与简单的效果相同:
In practice, this would give the same effect as the simpler:
UPDATE tblName SET colname='' WHERE colname LIKE '%''%';
您的实际用例要简单得多:您想替换所有出现的固定字符串('
,这需要用另一个固定字符串(空字符串,写成''''''
进行引号和转义) ''
)。因此,您根本不需要任何模式匹配,只需使用直接替换即可替换
:
Your actual use case is much simpler: you want to replace all occurrences of a fixed string ('
, which will need to be quoted and escaped as ''''
) with another fixed string (the empty string, written ''
). So you don't need any pattern matching at all, just straight replacement using replace
:
UPDATE tblName SET colname=replace(colname, '''', '');
如果将其限制为包含撇号开头的行,则可能会更快:
This will probably be faster if you limit it to rows that contain an apostrophe to begin with:
UPDATE tblName SET colname=replace(colname, '''', '') WHERE colname LIKE '%''%';
这篇关于替换正则表达式在PostgreSQL中不起作用的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!