REGEXP_REPLACE for exact regex pattern, not working

Question

I'm trying to match an exact pattern to do some data cleanup for ISSN's using the code below:

select case when REGEXP_REPLACE('1234-5678 ÿþT(zlsd?k+j''fh{l}x[a]j).,~!@#$%^&*()_+{}|:<>?`"\;''/-',  '([0-9]{4}[\-]?[Xx0-9]{4})(.*)', '$1') not similar to '[0-9]{4}[\-]?[Xx0-9]{4}' then 'NOT' else 'YES' end

The pattern I want match any 8 digit group with a possible dash in the middle and possible X at the end.

The code above works for most cases, but if capture group 1 is the following example: 123456789 then it also returns positive because it matches the first 8 digits, and I don't want it to.

I tried surrounding capture group 1 with ^...$ but that doesn't work either.

So I would like to match exactly these examples and similar ones:

BUT NOT THESE (and similar):

1234567899
1234567899x

What am I missing?

Try ^([0-9]{4}-?[Xx0-9]{4})([^0-9].*)?$. Replace [\-]? with -? everywhere, BTW. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Jun 12, 2019 at 16:02
Perfect, it works! Thank you. I do need need it to recognize a possible space as well as a dash, so I did this: ^([0-9]{4}[\\s|\-]?[Xx0-9]{4})([^0-9].*)? — Kenobi
– Kenobi, Commented Jun 12, 2019 at 16:22

Wiktor Stribiżew · Accepted Answer · 2019-06-12 16:12:07Z

4

You may use

^([0-9]{4}-?[Xx0-9]{4})([^0-9].*)?$

See the regex demo

Details

^ - start of string
([0-9]{4}-?[Xx0-9]{4}) - Capturing group 1 ($1): four digits, an optional -, and then four x / X or digits
([^0-9].*)? - an optional Capturing group 2: any char other than a digit and then any 0+ chars as many as possible
$ - end of string.

answered Jun 12, 2019 at 16:12

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

REGEXP_REPLACE for exact regex pattern, not working

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