How to make javascript regex work in postgresql regexp_matches?

Question

I'm looking to capture all percents that's less than 30% with max 4 decimal places in a document.

Here's a working javascript regex sample: https://regex101.com/r/iM3nX5/5

When I use this regex \b(?:[1-2]?[0-9]\.[0-9]{1,4})\b[^a-zA-Z\d<]{0,3}%? in Postgres, it doesn't work:

SELECT regexp_matches('11111
11111.
11111.1111
.11111
a111.1111
99
010
101
100
100.01
2.95%
19.5113%
5.32
0.0250
9.32
24.32
0.0023
30.20
29.23', '\b(?:[1-2]?[0-9]\.[0-9]{1,4})\b[^a-zA-Z\d<]{0,3}%?', 'g')

Any ideas on what I'm missing to make it work in Postgres?

Thanks in advance.

Wiktor Stribiżew · Accepted Answer · 2019-03-23 21:55:19Z

1

Word boundaries are a culprit. You need to use \m / \M to match leading/trailing word boundaries, or \y that is equivalent of \b. See the Table 9.20. Regular Expression Constraint Escapes:

\m   matches only at the beginning of a word
\M   matches only at the end of a word
\y   matches only at the beginning or end of a word

E.g. you may use

'\m(?:[1-2]?[0-9]\.[0-9]{1,4})\M[^a-zA-Z\d<]{0,3}%?'

Or

'\y(?:[1-2]?[0-9]\.[0-9]{1,4})\y[^a-zA-Z\d<]{0,3}%?'

See the PostgreSQL demo online. Results:

edited Mar 23, 2019 at 21:55

answered Mar 23, 2019 at 21:49

Wiktor Stribiżew

631k41 gold badges502 silver badges633 bronze badges

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

Collectives™ on Stack Overflow

How to make javascript regex work in postgresql regexp_matches?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related