Re: Integrity Constraint

From: Jonathan Gennick <jonathan_at_gennick.com>
Date: Sat, 27 Nov 1999 01:41:31 GMT
Message-ID: <384b352f.62611883@netnews.worldnet.att.net>

On 26 Nov 1999 16:03:37 GMT, cerit_at_teleordnospamplease.co.uk (Ceri) wrote:

>The field is a varchar2(11) and the data should be exactly 10 characters
>long, the first 9 being numeric and the last character may be numeric or a
>character.
>Unfortunately, checking this at the client application is not an option.
>The checking needs to be done by the database.

To verify the length, you could use the built-in LENGTH function as follows:

alter table x
  add (constraint check_length

         check (length(col)=10));

One possible approach to verifying that the first 9 characters are digits involves the translate function:

   translate(substr('yourstring',1,9),

                         '1234567890','1111111111')

If yourstring is nine digits,then the result of executing this function should be 111111111, so you could modify your check constraint to look like this:

alter table x
  add (constraint check_col

         check (length(col)=10 
                   and translate(substr(col,1,9), 
                         '1234567890','1111111111')
                         ='111111111'));

If you had to verify that the last character was an alpha or a digit, you could use another translate for that.

This may not be the most efficient or "best" approach, whatever "best" is. It's just the first approach that came to mind.

Jonathan

---

jonathan_at_gennick.com
http://gennick.com
Brighten the Corner Where You Are Received on Fri Nov 26 1999 - 19:41:31 CST