1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Help needed for zip code check constraint

Discussion in 'SQL PL/SQL' started by tastybrownies, Nov 3, 2009.

  1. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Hey all, I'm pretty new at using oracle and sql and I have a question I'm wondering if anyone can answer.

    I am trying to make a check constraint for a zip code field and I'm having some trouble, this is what I have so far.

    I want the first 5 numbers to be regular 0-9, followed by a dash(-) and then regular 0-9 again for the last 4 numbers. I got most of this working by using substr. I just can't figure out what to do with the middle - part.

    Code (SQL):
    ALTER TABLE STUDENT
    ADD CONSTRAINT STUDENT_ZIP_CK
    CHECK ( substr(ZIP,1,5) BETWEEN '0' AND '9'
    AND substr(ZIP,6,1) = BETWEEN 'A' AND 'Z'
    AND substr(ZIP,7,10) BETWEEN '0' AND '9');
     
     
  2. rtaylor

    rtaylor Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    Cedar Rapids, IA
    Is this what you are looking for?

    Code (Text):
    ALTER TABLE STUDENT
    ADD CONSTRAINT STUDENT_ZIP_CK
    CHECK (substr(ZIP,1,5) BETWEEN '0' AND '9'
      AND substr(ZIP,6,1) = '-'
      AND substr(ZIP,7,10) BETWEEN '0' AND '9');
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It would be if the zip code field were 16 characters long. SUBSTR takes a string, a starting character and the length of the substring; you fixed one coding error yet left the second untouched. For this to work it needs to be coded in this manner:

    Code (Text):
    ALTER TABLE STUDENT
    ADD CONSTRAINT STUDENT_ZIP_CK
    CHECK (substr(ZIP,1,5) BETWEEN '0' AND '9'
      AND substr(ZIP,6,1) = '-'
      AND substr(ZIP,7,4) BETWEEN '0' AND '9');
    The first SUBSTR() function checks the first five characters of the string, the next the sixth character of that string and the third checks the last four characters.
     
    ah.yasar likes this.
  4. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Thanks alot for the great responses guys, will try this as soon as I get home!
     
  5. tastybrownies

    tastybrownies Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    55
    Just like to say thanks again, the code you guys provided worked, thanks!