Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Check constraint for hexadecimal values

Re: Check constraint for hexadecimal values

From: <red_valsen_at_yahoo.com>
Date: 20 Jul 2007 10:29:35 -0700
Message-ID: <1184952340.730499.117980@r34g2000hsd.googlegroups.com>


On Jul 20, 11:11 am, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <red_val..._at_yahoo.com> a écrit dans le message de news: 1184938095.275452.323..._at_m3g2000hsh.googlegroups.com...
> On Jul 20, 2:27 am, Maxim Demenko <mdeme..._at_gmail.com> wrote:
>
>
>
> > Michel Cadot schrieb:
>
> > > <red_val..._at_yahoo.com> a écrit dans le message de news: 1184882576.408827.91..._at_o61g2000hsh.googlegroups.com...
> > > | I've attempted to create a check constraint on a column which will
> > > | contain up to 8 individual hexadecimal values (e.g., "FF1C3D0A", etc.)
> > > | like so:
> > > |
> > > | create table bla(
> > > | .
> > > | .
> > > | column hexvalue char(8)
> > > | check (hexvalue in ('a-f''A-F''0-9')),
> > > | .
> > > | .
> > > | )
> > > |
> > > | -- but the syntax is incorrect. What will work? Do I need a regular
> > > | expression? Use 'like'?
> > > |
> > > | The requirements for check constraints are rather severe, so I'm
> > > | beginning to doubt that I can do this.
> > > |
>
> > > SQL> create table t (
> > > 2 hexvalue varchar2(8)
> > > 3 check (to_number(hexvalue,'XXXXXXXX')<to_number('100000000','XXXXXXXXX'))
> > > 4 )
> > > 5 /
>
> > > Table created.
>
> > > Regards
> > > Michel Cadot
>
> > Nice approach.
> > Yet another idea:
>
> > SQL> create table t (
> > 2 hexvalue varchar2(8)
> > 3 check (length(hextoraw(hexvalue))<=8)
> > 4 )
> > 5 /
>
> > Table created.
>
> > Best regards
>
> > Maxim
>
> Thanks, Oracle experts. This variation seems to work:
>
> create table bla(
> hexvalue varchar2(8)
> check (to_number(hexvalue, 'XXXXXXXX') > -1 ))
>
> ----------------------------
>
> Not really Oracle works on more than 4 bytes number:
>
> SQL> select * from dual where to_number('100000000','XXXXXXXXX')>-1;
> D
> -
> X
>
> 1 row selected.
>
> Regards
> Michel

Michel,

What am I missing in your last? Your SQL statement will always be true and return all rows since 4294967296 > -1. Received on Fri Jul 20 2007 - 12:29:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US