SQL Check Statement for Zip Code using escape character

I have tried looking in other areas, but I can't seem to figure out what is wrong with what I am doing. I am trying to create a check constraint on a column that is supposed to contain a 9 digit zip code formatted as "00000-0000". This is for an assignment, so the only thing I am allowed to do is create the check constraint.

I have already used the check statement successfully for other columns, but for some reason the statement that I found here on Stack Overflow does not work. The only allowed characters are numbers and the hyphen character ('-').

alter table Student
    add constraint student_zip_ck
    check (Zip not like '%[0-9\-]%' escape '\');

Since this check constraint was modelled after another (positively-rated) question on Stack Overflow, I don't know what could be wrong. This is the error I receive.

Error starting at line 751 in command:
alter table Student
  add constraint student_zip_ck
  check (Zip not like '%[0-9\-]%' escape '\')
Error report:
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-01424: missing or illegal character following the escape character
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.

Does anyone have suggestions, questions, or comments for me?

Solution

You're missing anything related to regular expressions, which is probably why it isn't working. Your syntax implies you meant to use them and I would agree with that in this situation. Regular expressions have been available since Oracle 10g so you have to ensure that you're using this version or higher.

The syntax would become:

alter table Student
    add constraint student_zip_ck
    check (regexp_like(Zip,'^[[:digit:]]{5}-[[:digit:]]{4}$'));

This means:

^ - pin to the beginning of the string
[[:digit:]] - accept only numeric values. This is the POSIX compliant variation and is equivalent to \d.
{5} - 5 times exactly
- - match a hyphen
$ - pin to the end of a string

To make the hyphen and the second for digits optional you need to create a group using (). This makes everything within the parenthesis a single unit, whether it's a string or another regular expression, which you can then apply other operators to. A ? means match 0 or 1 times, and needs to be applied to the group. Putting it all together you get:

regexp_like(Zip,'^[[:digit:]]{5}(-[[:digit:]]{4})?$')

SQL Check Statement for Zip Code using escape character

Further Reading