Oracle Pattern Matching Regular Expression REGEXP_LIKE to POSTGRES Equivalent?

Question

Any idea on what would be the right function in Postgres to convert the Oracle Pattern Matching Regular Expression REGEXP_EXP ? I'm converting the source code from Oracle to Postgres,

ORACLE

IF REGEXP_LIKE(j.TRK, '^[0-9]+$') THEN

For Postgres we are using the following code,

IF aws_oracle_ext.regexp_like(j.trk, '^[0-9]+$') THEN

I need something native to Postgres without Oracle Extensions.

Use the ~ operator in Postgres

Kaushik Nayak
– Kaushik Nayak

2018-11-01 17:41:39 +00:00
Commented Nov 1, 2018 at 17:41 — Kaushik Nayak
– Kaushik Nayak, Commented Nov 1, 2018 at 17:41
POSIX Regular Expressions

klin
– klin

2018-11-01 17:59:01 +00:00
Commented Nov 1, 2018 at 17:59 — klin
– klin, Commented Nov 1, 2018 at 17:59
if j.trk ~ '^[0-9]+$' then...

user330315
– user330315

2018-11-02 11:09:31 +00:00
Commented Nov 2, 2018 at 11:09 — user330315
– user330315, Commented Nov 2, 2018 at 11:09

Kirk Roybal · Accepted Answer · 2018-11-02 11:10:03Z

2

Postgresql has an operator for that ~ . This operator maps to the internal function textregexeq(), which you may also use explicitly if you want to keep your existing syntax.

The internal function is defined as:

boolean pg_catalog.textregexeq(text, text)
IMMUTABLE PARALLEL SAFE STRICT

edited Nov 2, 2018 at 11:10

user330315

answered Nov 2, 2018 at 10:52

Kirk Roybal

18k2 gold badges34 silver badges39 bronze badges

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

Collectives™ on Stack Overflow

Oracle Pattern Matching Regular Expression REGEXP_LIKE to POSTGRES Equivalent?

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