Postgresql-如何使用正则表达式模式提取字符串中子字符串的首次出现? [英] Postgresql - How do I extract the first occurence of a substring in a string using a regular expression pattern?
问题描述
我试图使用正则表达式从文本列中提取一个子字符串,但是在某些情况下,字符串中存在该子字符串的多个实例。
I am trying to extract a substring from a text column using a regular expression, but in some cases, there are multiple instances of that substring in the string.
在这些情况下,我发现查询没有返回子字符串的第一次出现。有人知道我在做什么错吗?
In those cases, I am finding that the query does not return the first occurrence of the substring. Does anyone know what I am doing wrong?
例如:
如果我有此数据:
create table data1
(full_text text, name text);
insert into data1 (full_text)
values ('I 56, donkey, moon, I 92')
我正在使用
UPDATE data1
SET name = substring(full_text from '%#"I ([0-9]{1,3})#"%' for '#')
,而我想获得'I 56'
而不是'I 92'
推荐答案
您可以使用 regexp_matches()
代替:
update data1
set full_text = (regexp_matches(full_text, 'I [0-9]{1,3}'))[1];
由于未传递其他标志,因此 regexp_matches()
仅返回第一个匹配项-但它返回一个数组,因此您需要从结果中选择第一个(也是唯一一个)元素(这是 [1]
部分)
As no additional flag is passed, regexp_matches()
only returns the first match - but it returns an array so you need to pick the first (and only) element from the result (that's the [1]
part)
最好将更新限制为只与正则表达式匹配的行:
It is probably a good idea to limit the update to only rows that would match the regex in the first place:
update data1
set full_text = (regexp_matches(full_text, 'I [0-9]{1,3}'))[1]
where full_text ~ 'I [0-9]{1,3}'
这篇关于Postgresql-如何使用正则表达式模式提取字符串中子字符串的首次出现?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!