Re: Check contsraint problem
Date: 1995/10/26
Message-ID: <46pg0b$pk1_at_newsbf02.news.aol.com>#1/1
Subject: Re: Check contsraint problem
From: phollard_at_bambam.turner.com (Pierre Hollard)
Date: 26 Oct 1995 17:36:06 GMT
Message-ID: <46ogu6$ou0_at_tbsnames.turner.com>
In article <carold-261095121306_at_carold-mac.logica.co.uk>,
carold_at_lna.logica.com (Carol Dionne) writes:
> I'm new to Oracle (I'm more comfortable with Sybase) and can't
determine
> how to do the following from the Oracle manuals. Any suggestions?
>
> I like to create a check constraint on an column of type char to test
that
> the assigned value is restricted to the characters of 0-9,A-F. I'm
using
> Oracle
> 7.1.6.2.
>
> In Sybase I would use a like clause with the pattern
> "[0-9,A-F][0-9,A-F][0-9,A-D][0-9,A-F]". My Oracle documentation doesn't
> mention
> any support of the [] symbols for testing a single character to be
within
> a specified range. I've looked at all supplied functions and didn't
see
> one
> that would help.
>
> Any suggestions on how to write this check constraint?
1st Response:
>Carol,
>You may try to use the TRANSLATE and RPAD functions. Try something like:
>if translate(char_value, '0123456789ABCDEF', 'XXXXXXXXXXXXXXXX')
> = rpad('X',length(char_value), 'X')
>then
> -- char value is OK
>else
> -- char value is NOT OK!!!!
>end if;
>------------------------------------------------------------------------- ----- >Pierre Hollard >Oracle Database Administrator Phone: (404) 827-0656 >Turner Broadcasting System, Inc. E-Mail:phollard_at_dev1.turner.com
>-------------------------------------------------------------------------
My comments:
The block of code above is not an integrity constraint. It could, however, be used as a trigger or in the application itself. The use of the CHECK integrity constraint would be:
CHECK ( col-name IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F') )
It's not very pretty, though... Received on Thu Oct 26 1995 - 00:00:00 CET