Postgres regexp_matches between two patterns

Question

I am trying to split the expression like in Postgres 9.4: "some text 123_good_345 and other text 123_some_invalid and 222_work ok_333 stop."

using pattern: (\d+\_.*\_\d+\D)+? result is:

"123_good_345"
"123_some_invalid and 222_work ok_333"

But I need

"123_good_345"
"222_work ok_333"

note, ignoring "123_some_invalid"

Please help!

On what basis do you decide that 222_work and ok_333 are part of same match group? — Kaushik Nayak
– Kaushik Nayak, Commented Dec 12, 2018 at 16:51
expression must start with "number + underscore" and end with "underscore + number", but "number + underscore some text number + underscore ... underscore + number" is wrong — man2002ua
– man2002ua, Commented Dec 12, 2018 at 17:08
You need \d+_(?:(?!\d_).)*_\d+. See regex101.com/r/iq45qc/1 and rextester.com/VJJXF1223. See full answer below. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Dec 12, 2018 at 17:09

Wiktor Stribiżew · Accepted Answer · 2018-12-13 10:14:17Z

1

You may use

\d+_(?:(?!\d_).)*_\d+

See the regex demo. Or, if there can be no digits between \d+_ and _\d+, use

\d+_\D+_\d+

See this regex demo.

Details

\d+ - 1 or more digits -_ - an underscore
(?:(?!\d_).)* - any char, 0 or more repetitions, as many as possible, that does not start a digit + _ char sequence
\D+ - any 1+ chars other than digits
_ - an underscore
\d+ - 1+ digits.

See the PostgreSQL demo:

SELECT unnest(regexp_matches('some text 123_good_345 and other text 123_some_invalid and 222_work ok_333 stop.', '\d+_(?:(?!\d_).)*_\d+', 'g'));

or

SELECT unnest(regexp_matches('some text 123_good_345 and other text 123_some_invalid and 222_work ok_333 stop.', '\d+_\D+_\d+', 'g'));

edited Dec 13, 2018 at 10:14

answered Dec 12, 2018 at 17:13

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

16 Comments

man2002ua Over a year ago

Almost, but it is ok for Postgres 9.6, while in Postgres 9.4 it returns: "123_good_345 and other text 123" for the first line, while second line is correct :-/

Wiktor Stribiżew Over a year ago

@man2002ua Strange, try \d+_(?:(?!\d_).)*?_\d+. Or \d+_[^_]*_\d+.

man2002ua Over a year ago

first suggestion - no changes, second - only last expression shown (but correct)

Wiktor Stribiżew Over a year ago

@man2002ua What is the code behind that? It is quite clear to me there is something that modifies your input, or you just have an input different from what you posted.

user1602492 Over a year ago

Does '\d+_[^\d]+_\d+' for you?

|

Collectives™ on Stack Overflow

Postgres regexp_matches between two patterns

1 Answer 1

16 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Related