Re: Help needed enforcing constraints..

From: Julian Goulet <jgoulet_at_whsun2.whoi.edu>
Date: 1996/08/27
Message-ID: <4vuol4$r4c_at_dilbert.whoi.edu>#1/1


Dan Roberts (Roberts_Daniel_G.PriLVMS3_at_MSMAIL.BMS.COM) wrote:
: Hi Netters
 

: .. I am trying to enforce a constraint on making sure that an order number
: is entered in correctly.. IE.. It must have format values
: [A-Z][A-Z][A-Z][0-9][0-9] in order to be a valid number. so if someone
: were to enter in an order number as lets say bmss3
: or b3m91 they would get an error msg..becuase the correct format would
: have to be bms99 in this case. Now I have figured
: out how to do this in a model example below..but it is very poor since I
: had to make each character a different attribute and
: enforce the constraints one at a time..and in the long run I would end up
: with a huge concatenated primary key...
  

assign a constraint:

  check ( (substr(ordnum,1,1) between 'A' and 'Z') and

  		(substr(ordnum,2,1) between 'A' and 'Z') and
		...
		(substr(ordnum,4,1) between '0' and '9') and
		...
	)

  if that doesn't work [ I did not try it - between may take only numeric values ] then replace :         

        substr(ordnum,1,1) between 'A' and 'A'

        with:

        ascii(substr(ordnum,1,1)) between 65 and 90

        etc., etc,

  good luck

  Julien R Goulet Jr
  jgoulet_at_mola.gso.uri.edu Received on Tue Aug 27 1996 - 00:00:00 CEST

Original text of this message