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](https://regex101.com/r/adbsts/1) — Pushpesh Kumar Rajwanshi, Apr 25 '19 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, Apr 25 '19 at 17:00
For underscore.. I tried this https://stackoverflow.com/questions/38084864/how-to-escape-underscores-in-postgresql — Ram, Apr 25 '19 at 17:20
I think the tilde `~` returns a boolean. See https://www.postgresql.org/docs/11/functions-matching.html Try using REGEXP_MATCHES from the demo. — The fourth bird, Apr 25 '19 at 18:39
REGEXP_MATCHES does not work with where clause. Not sure how to use this with where or how to make the select work.. — Ram, Apr 25 '19 at 19:01
@Ram Do you mean like this? https://rextester.com/VQUNHP73524 — The fourth bird, Apr 26 '19 at 08:33

The fourth bird · Accepted Answer · 2019-04-26T08:42:27.670

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

edited Apr 26 '19 at 08:42

answered Apr 25 '19 at 16:57

The fourth bird

1 Answers1