Having difficulty in pattern matching Postal Codes for an oracle regexp_like command

userB4lU 0
Viewed 43

The Problem: All I'm trying to do is come up with a pattern matching string for my regular expression that lets me select Canadian postal codes in this format: 'A1A-2B2' (for example).

The types of data I am trying to insert:

Insert Into Table
    (Table_Number, Person_Name, EMail_Address, Street_Address, City, Province, Postal_Code, Hire_Date) 
Values
    (87, 'Tommy', 'mobster@gmail.com', '123 Street', 'location', 'ZY', 'T4X-1S2', To_Date('30-Aug-2020 08:50:56');

This is a slightly modified/generic version to protect some of the data. All of the other columns enter just fine/no complaints. But the postal code it does not seem to like when I try to run a load data script.

The Column & Constraint in question:

Postal_Code varchar2(7) Constraint Table_Postal_Code Null 
                        Constraint CK_Postal_Code Check ((Regexp_like (Postal_Code, '^\[[:upper:]]{1}[[:digit:]]{1}[[:upper:]][[:punct:]]{1}[[:digit:]]{1}[[:upper:]](1}[[:digit:]]{1}$')),

My logic here: following the regular expression documentation: I have:

  • an open quote
  • a exponent sign to indicate start of string
  • Backslash (I think to interpet a string literal) -1 upper case letter, 1 digit, 1 uppercase , 1 :punct: to account for the hypen, 1 digit, 1 upper case letter, 1 digit
  • $ to indicate end of string
  • Close quote

In my mind, something like this should work, it accounts for every single letter/character and the ranges they have to be in. But something is off regarding my formatting of this pattern matching string.

The error I get is:

ORA-02290: check constraint (user.CK_POSTAL_CODE) violated

(slightly modified once more to protect my identity)

Which tells me that the data insert statement is tripping off my check constraint and thats about it. So its as issue with the condition of the constraint itself - ie string I'm using to match it. My instructor has told me that insert data is valid, and doesn't need any fix-up so I'm at a loss.

Limits/Rules: The Hyphen has to be there/matched to my understanding of the problem. They are all uppercase in the dataset, so I don't have to worry about lowercase for this example.

I have tried countless variations of this regexp statement to see if anything at all would work, including:

  • changing all those uppers to :alpha: , then using 'i' to not check for case sensitivity for the time being
  • removing the {1} in case that was redudant
  • using - (backslash hyphen) , to turn into a string literal maybe
  • using only Hyphen by itself
  • even removing regexp altogether and trying a LIKE [A-Z][0-9][A-Z]-[0-9][A-Z][0-9] etc
  • keeping the uppers , turning :digit:'s to [0-9] to see if that would maybe work

The only logical thing I can think of now is: the check constraint is actually working fine and tripping off when it matches my syntax. But I didn't write it clearly enough to say "IGNORE these cases and only get tripped/activated if it doesn't meet these conditions"

But I'm at my wits end and asking here as a last resort. I wouldn't if I could see my mistake eventually - but everything I can think of, I probably tried. I'm sure its some tiny formatting rule I just can't see (I can feel it).Thank you kindly to anyone who would know how to format a pattern matching string like this properly.

postal-code plsql oracle10g oracle regex
3 Answers

You do not need the backslash and you have (1} instead of {1}.

You can simplify the expression to:

Postal_Code varchar2(7)
            Constraint Table_Postal_Code Null 
            Constraint CK_Postal_Code Check (
              REGEXP_LIKE(Postal_Code, '^[A-Z]\d[A-Z][[:punct:]]\d[A-Z]\d$')
            )

or:

Constraint CK_Postal_Code Check (
  REGEXP_LIKE(
    Postal_Code,
    '^[A-Z][0-9][A-Z][[:punct:]][0-9][A-Z][0-9]$'
  )
)

or:

Constraint CK_Postal_Code Check (
  REGEXP_LIKE(
    Postal_Code,
    '^[[:upper:]][[:digit:]][[:upper:]][[:punct:]][[:digit:]][[:upper:]][[:digit:]]$'
  )
)

or (although the {1} syntax is redundant here):

Constraint CK_Postal_Code Check (
  REGEXP_LIKE(
    Postal_Code,
    '^[[:upper:]]{1}[[:digit:]]{1}[[:upper:]]{1}[[:punct:]]{1}[[:digit:]]{1}[[:upper:]]{1}[[:digit:]]{1}$'
  )
)

fiddle

removing regexp altogether and trying a LIKE [A-Z][0-9][A-Z]-[0-9][A-Z][0-9] etc

That will not work as the LIKE operator does not match regular expression patterns.

MT00

It looks like you may have been overcomplicating the regex a bit. The regex below matches your description based on the first set of bullets you lined out:

REGEXP_LIKE (postal_code, '^[A-Z]\d[A-Z]-\d[A-Z]\d$')
EJ Egyed0

I see two problems with that regexp.

Firstly, you have a spurious \ at the start. It serves you no purpose, get rid of it.

Secondly, the second-from last {1} appears in your code with mismatched brackets as (1}. I get the error ORA-12725: unmatched parentheses in regular expression because of this.

To be honest, you don't need the {1}s at all: they just tell the regular expression that you want one of the previous item, which is exactly what you'd get without them.

So you can fix the regexp in your constraint by getting rid of the \ and removing the {1}s, including the one with mismatched parentheses.

Here's a demo of the fixed constraint in action:

SQL> CREATE TABLE postal_code_test (
  2      Postal_Code varchar2(7) Constraint Table_Postal_Code Null
  3                          Constraint CK_Postal_Code Check ((Regexp_like (Postal_Code, '^[[:upper:]][[:digit:]][[:upper:]][[:punct:]][[:digit:]][[:upper:]][[:digit:]]$'))));

Table created.

SQL> INSERT INTO postal_code_test (postal_code) VALUES ('T4X-1S2');

1 row created.

SQL> INSERT INTO postal_code_test (postal_code) VALUES ('invalid');
INSERT INTO postal_code_test (postal_code) VALUES ('invalid')
*
ERROR at line 1:
ORA-02290: check constraint (user.CK_POSTAL_CODE) violated
Luke Woodward0
Related