Regex in PostgreSQL doesn't work as expected

Question

ABCD9876S__9999.A001
ABCD9876S__9999.A002
- Always starts with ABCD
- Followed by a mix of digits and alphabets
- Followed by two underscores __
- Followed by 9999.A00 1 or 2

I want to catch the above two strings using reg ex

I have :

ABCD.*9999\.A00[12]

This doesn't work in Postgres. How do I convert this? Is there an online tool?

Try using this regex ^ABCD[a-zA-Z0-9]+__9999\.A00[12]$ Demo — Pushpesh Kumar Rajwanshi
– Pushpesh Kumar Rajwanshi, Commented Apr 25, 2019 at 16:24
It doesn't work.. As soon as I do the 9999 it fails. Doesn't error out but doesnt pull the data. I am using "where Name ~ ('ABCD[0-9a-zA-Z].*') " which works. But finally I want to be able to choose between 1 and 2 meaning A001 or A002 — Ram
– Ram, Commented Apr 25, 2019 at 17:00

The fourth bird · Accepted Answer · 2019-04-26 08:42:27Z

2

You could specify the character ranges that you want to allow using a character class and add the double undescore you want to be part of the match.

ABCD[a-zA-Z0-9]+__9999\.A00[12]

If the match should be from the start of the string use anchors ^ and $

See a postgre sql demo 1 | demo 2 using regexp_matches

answered Apr 25, 2019 at 16:57

The fourth bird

165k16 gold badges61 silver badges75 bronze badges

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

1 Answer 1