Postgresql regexp_matches syntax not working as expected

Question

I use the Postgres regexp_matches function to extract numbers.

The regular expression I use is

4([\s\-\/\.]*?0){3}([\s\-\/\.]*?[12]){1}([\s\-\/\.]*?\d){4}

If I use a tool like https://regexr.com/ to verify if it's working and I apply the following test set

4-0001-1234
5-2342-2344
499999999
4-0001-1234 4.0001.12344  4-0-0-0-1-1234

I get the expected extraction result:

4-0001-1234
4-0001-1234
4.0001.1234
4-0-0-0-1-1234

However, if I use the same expression in Postgresql, it does work well:

SELECT unnest(regexp_matches('4-0001-1234', '4([\s\-\/\.]*?0){3}([\s\-\/\.]*?[12]){1}([\s\-\/\.]*?\d){4}', 'g'));

Result:

0
1
4

What I suspect is, that it has to do with the greediness and/or that the quantifiers like {3} are not applied in the right way. Or it uses the Posix standard for regular expressions, which always seem to differ a little bit from the Java Syntax.

Any suggestions why it is not working and how to fix it?

regexr shows exactly the same results if you hover over the match: "group #1: 0; group #2: 1; group #3: 4" — melpomene
– melpomene, Commented Jan 5, 2018 at 9:05

Wiktor Stribiżew · Accepted Answer · 2018-01-05 09:04:01Z

2

The regexp_matches(string text, pattern text [, flags text]) function returns the captured values:

Return all captured substrings resulting from matching a POSIX regular expression against the string.

You may fix the expression using non-capturing groups:

SELECT unnest(regexp_matches('4-0001-1234 4.0001.12344  4-0-0-0-1-1234', '4(?:[\s/.-]*0){3}(?:[\s/.-]*[12])(?:[\s/.-]*\d){4}', 'g'));

See the online demo.

BTW, you do not need to escape - when it is at the start/end of the bracket expression, and there is no need to escape neither / nor . there. I also suggest removing {1} as a = a{1} in any regex supporting limiting quantifiers.

answered Jan 5, 2018 at 9:04

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

Collectives™ on Stack Overflow

Postgresql regexp_matches syntax not working as expected

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related