Postal Code Constraint

Hey everyone!

I'm currently working on a project for school, and I can't wrap my brain around one of the constraints for our Canadian postal code. It's alpha numeric for those who don't know, and it looks like T1W1W1. I am not exactly sure how to do a check on this to ensure that it's in that format.

Any insight is greatly appreciated.

Thanks!

Jared

Hi Jarednielsen
Check this

Assuming The Postal Codes Exists Between t1w1v1-t9w9v9.
SQL> ed
Wrote file afiedt.buf

1* create table a(code varchar2(10) check(code>='t1w1v1' and code<='t9w9v9'))
SQL> /

Table created.

SQL> insert into table a('&code');
Enter value for code: a
old 1: insert into table a('&code')
new 1: insert into table a('a')
insert into table a('a')
*
ERROR at line 1:
ORA-00903: invalid table name


SQL> ed
Wrote file afiedt.buf

1* insert into table a values('&code')
SQL> /
Enter value for code: t1w1v1
old 1: insert into table a values('&code')
new 1: insert into table a values('t1w1v1')
insert into table a values('t1w1v1')
*
ERROR at line 1:
ORA-00903: invalid table name


SQL> ed
Wrote file afiedt.buf

1* insert into a values('&code')
SQL> /
Enter value for code: t1w1v1
old 1: insert into a values('&code')
new 1: insert into a values('t1w1v1')

1 row created.

SQL> /
Enter value for code: t2w3v3
old 1: insert into a values('&code')
new 1: insert into a values('t2w3v3')

1 row created.

SQL> /
Enter value for code: a1w2v3
old 1: insert into a values('&code')
new 1: insert into a values('a1w2v3')
insert into a values('a1w2v3')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C003034) violated


SQL> /
Enter value for code: t1v6w3
old 1: insert into a values('&code')
new 1: insert into a values('t1v6w3')
insert into a values('t1v6w3')
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C003034) violated


SQL> /
Enter value for code: t2w2v6
old 1: insert into a values('&code')
new 1: insert into a values('t2w2v6')

1 row created.

so when ever we try to violate the series of "TWV" it will not accept.
Hope This Is Helpful,If Not Pls Get Back With An Example.
Happy Coding
sasidharis@yahoo.com

Do you wnat to only insert the canadian postal code or american also?? If only canadian then follwoing code might be useful for you, this code checks the total lenght of the postal code that should be siz and the sorting order of latter, like first letter should be character, second numeric, third character,fourth numeric, fifth character, and sixth numeric.
CREATE TABLE postal_codes
(postal_code varchar2(6)
check ('X9X9X9'=translate(postal_code,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'9999999999XXXXXXXXXXXXXXXXXXXXXXXXX'))
)
/
new add the postal code you wnat, for example my postal code is K1V9S2.

insert into postal_codes('K1V9S2')
/
1 row created.

now I am trying to add some faulty data, for example 'K1V' and 'K1VB2G'

insert into postal_codes('K1VB2G') -- format does not match
/
error will be occured

insert into postal_codes('K1V') -- shorten of length
/
error will be occured


Regards,