Re: Check constraint for hexadecimal values

<red_valsen_at_yahoo.com> a écrit dans le message de news: 1184938095.275452.323160_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 Received on Fri Jul 20 2007 - 10:11:43 CDT