Postgres Regex: extract string after the last occurence of the pattern and the end of line

Question

Please help me to extract the text after the last occurrence of the Schedule : and the end of the line.

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

In the above case, I need to extract Expect disruption everyday between 20:00 and 06:00 from 26 October 2020 to 31 October 2020

So far I came up only with the following:

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

But it doesn't work in Postgres. It returns error: invalid regular expression: invalid escape \ sequence

I also tried to replace \s and \S with [:space:] and ^[:space:] as per Postgres documentation but it doesn't work either

Thanks in advance.

In PostgreSQL, you do not need [\s\S], just use a dot, .. Thus, you need (?<=Schedule : )[^\r\n]+(?!.*Schedule) — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Dec 3, 2020 at 8:41

Wiktor Stribiżew · Accepted Answer · 2020-12-03 08:45:24Z

3

Since a . in a PostgreSQL regex matches any char including line break chars, you need to introduce two changes:

The first .* should be replaced with [^\r\n]+ to match any chars other than common line break chars
The [\s\S] in the lookahead should be replaced with a mere ..

You can use

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

See the online demo:

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')

Output:

answered Dec 3, 2020 at 8:45

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Wiktor Stribiżew Over a year ago

@DmytroSly Just FYI, you do not need the lookbehind, just use a capturing group around the pattern part you need to extract, 'Schedule\s*:\s*([^\r\n]+)(?!.*Schedule)' would work the same here.

Collectives™ on Stack Overflow

Postgres Regex: extract string after the last occurence of the pattern and the end of line

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related