Postgres Regex：在模式的最后一次出现和行尾之后提取字符串

debugcn 发表于 Dev

Dmytro Sly

请帮助我在最后一次出现Schedule : 和行尾之后提取文本。

Lane Closures : Lane one will be closed
Reason : Roadworks are planned
Status : Pending
Schedule : Expect disruption everyday between 20:00 and 06:00 from 5 October 2020 to 9 October 2020
Schedule : Expect disruption everyday between 20:00 and 06:00 from 12 October 2020 to 16 October 2020
Schedule : Expect disruption everyday between 20:00 and 06:00 from 19 October 2020 to 23 October 2020
Schedule : Expect disruption everyday between 20:00 and 06:00 from 26 October 2020 to 31 October 2020
Lanes Closed : There will be one of two lanes closed

在上述情况下，我需要提取 Expect disruption everyday between 20:00 and 06:00 from 26 October 2020 to 31 October 2020

到目前为止，我只想到以下几点：

(?<=Schedule : ).*(?![\s\S]*Schedule)

但这在Postgres中不起作用。它返回错误：invalid regular expression: invalid escape \ sequence

我也试图取代\s，并\S以[:space:]和^[:space:]为每个Postgres的文档，但它也不起作用

提前致谢。

维克多·史翠比维

由于.PostgreSQL正则表达式中的a匹配包括换行符char在内的任何char，因此您需要进行两个更改：

第一个.*应该替换[^\r\n]+为匹配除公共换行符以外的任何其他字符
将[\s\S]在先行应该仅仅被替换.。

您可以使用

(?<=Schedule : )[^\r\n]+(?!.*Schedule)

参见在线演示：

SELECT REGEXP_MATCHES(
    E'Lane Closures : Lane one will be closed\nReason : Roadworks are planned\nStatus : Pending\nSchedule : Expect disruption everyday between 20:00 and 06:00 from 5 October 2020 to 9 October 2020\nSchedule : Expect disruption everyday between 20:00 and 06:00 from 12 October 2020 to 16 October 2020\nSchedule : Expect disruption everyday between 20:00 and 06:00 from 19 October 2020 to 23 October 2020\nSchedule : Expect disruption everyday between 20:00 and 06:00 from 26 October 2020 to 31 October 2020\nLanes Closed : There will be one of two lanes closed', 
    '(?<=Schedule : )[^\r\n]+(?!.*Schedule)', 
    'g')

输出：