CODEWITHSUNDEEP
regexpostgresql
Five Dobies
Five Dobies

Reputation: 13

Regex for a certain number of digits

I need to select rows from postgres where a part number has a three digit number (300-399) near the beginning. I just need a match/no match.

I'm mostly there with:

WHERE name ~ '^[A-Z]{0,5}3[0-9]{2}[^0-9]?*'

The part numbers

  1. can be just 3 digits long,
  2. can have up to 5 characters before the number
  3. can have characters after the number
  4. must exclude 4 digit numbers

These should match:

These should not match:

With the above, the 'should match' pass, but the 'should not match' are also passing. It seems the question mark that checks for a non-digit ([^0-9]?) allows the digits through, but I can't make it required since a simple 3-digit part number would not match.

Thanks!

Upvotes: 1

Views: 2445

Answers (2)

The fourth bird
The fourth bird

Reputation: 163632

The pattern you tried could possibly also match the first 3 digits in a string with 4 digits as the character class at the end is optional [^0-9]?

If you don't make it optional [^0-9], it would not match a only 323 as there is a character expected after it which is any char except a digit.

If there can be characters after the number, but not a digit, you can use a negative lookahead (?!\d) to assert not a digit directly to the right.

^[A-Z]{0,5}3[0-9]{2}(?!\d)

Explanation

  • ^ Start of string
  • [A-Z]{0,5} Match 0-5 times a char A-Z
  • 3[0-9]{2} Match 3 and 2 digits
  • (?!\d) Negative lookahead, assert what is directly to the right is not a digit

Regex demo | Postgresql demo

Upvotes: 0

Mike Organek
Mike Organek

Reputation: 12494

This regexp passes all your tests.

 '^[^\d]{0,5}\d{1,3}(\y|[^\d])'

The first caret ^ anchors to the start.

The [^\d]{0,5} allows up to five non-digit characters.

The \d{1,3} allows one to three each digit characters.

The (\y|[^\d]) alternation matches either a non-digit character or a word boundary such as the end of the string.

Upvotes: 1

Related Questions