CODEWITHSUNDEEP
regexamazon-redshiftregexp-replace
Kenobi
Kenobi

Reputation: 485

REGEXP_REPLACE for exact regex pattern, not working

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:

1234-5678 
1234-567X 
12345678 
1234567X

BUT NOT THESE (and similar):

1234567899
1234567899x

What am I missing?

Upvotes: 2

Views: 560

Answers (1)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626748

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.

Upvotes: 4

Related Questions