Re: How to allow only Alphanumeric for varchar?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/15
Message-ID: <8ibdk8$d06$1@nnrp1.deja.com>

In article <3948BB3E.551B_at_yahoo.com>,
  connor_mcdonald_at_yahoo.com wrote:
> Thomas J. Kyte wrote:
> >
> > In article <8i8ifp$a2n$1_at_slb0.atl.mindspring.net>,
> > "Amanda Zaborowski" <azaborow_at_mindspring.com> wrote:
> > > I have a question that sould be an easy one, yet I cannot find it
 anywhere.
> > > Here is my situation:
> > >
> > > * What I would like to do is make some of the fields constrained
 to
 allow
> > > only alphanumeric characters. (ie - first name - we do not want
 user
 to be
> > > able to enter 12345 rather than a name in the 'first name' field.)
> > >
> > > 1. We are using ERwin datamodeling tool. I will then forward
 engineer this
> > > to auto create the DB in Oracle. In ERwin, you can specify that a
 number
> > > can only be valid in range of, for example, 1 to 50. I assume you
 can do
> > > this for varchars, allowing only alphanumeric chars, no numbers,
 etc?
> > >
> > > 2. We are using Developer 2000 Forms for front end. Is there
 some
 kind of
> > > formatting I can do so that user can only enter in alphanumeric
 chars?
> > >
> > > 3. Obviously, we have an Oracle DB - is there code I could use
 in
 creating
> > > the table that constrains the chars to alphanumeric?
> > >
> > > Can anyone help if this is possible in ERwin, Oracle itself, or
 Developer
> > > Forms?
> > >
> > > Thanks in advance!
> > >
> > >
> >
> > add a check constraint to the table such that:
> >
> > ops$tkyte_at_8i> create table t
> > 2 ( x varchar(25)
> > 3 constraint x_must_be_ALPHA
> > 4 check ( replace(
> > 5 translate(
> > 6 upper(x),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad
 ('x',26,'x')
> > 7 ), 'x','') is null )
> > 8 );
> >
> > Table created.
> >
> > ops$tkyte_at_8i>
> > ops$tkyte_at_8i> insert into t values ( 'abc' );
> >
> > 1 row created.
> >
> > ops$tkyte_at_8i> insert into t values ( 'ab1' );
> > insert into t values ( 'ab1' )
> > *
> > ERROR at line 1:
> > ORA-02290: check constraint (OPS$TKYTE.X_MUST_BE_ALPHA) violated
> >
> > that uppercases the field, turns all A-Z characters into 'x', turns
> > all 'x's into nothing and checks to make sure the string
 is "nothing".
> > That allows either a string with A-Z, a-z to be input or a NULL
 string
> > to be input (add a NOT NULL if you don't want nulls)
> >

>

> Hello Thomas,

>
> Just curious on the performance of this versus similar code in a
 trigger
> (mainly so the error msg could be customised)...

>

> (For anyone still at work - care to post some timings ?)

>

> Cheers
> COnnor

>

> --
> ===========================================
> Connor McDonald
> http://www.oracledba.co.uk

>

> We are born naked, wet and hungry...then things get worse
>

the table with a trigger is much slower. insert 1000 rows;

no trigger, no constraint       Elapsed: 00:00:00.55
no trigger, YES constraint      Elapsed: 00:00:00.55
YES trigger, no constraint      Elapsed: 00:00:03.09

things declaritive are almost much much faster then interpreted code.

ops$tkyte_at_DEV8I.WORLD> drop table t;

Table dropped.

Elapsed: 00:00:00.22
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> create table t
  2 (
  3 x varchar(25)
  4 )
  5 /

Table created.

Elapsed: 00:00:00.05

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> begin
  2          for i in 1 .. &1 loop
  3                  insert into t values ( rpad( 'x', 25, 'x' ) );
  4          end loop;

  5 end;
  6 /

old   2:        for i in 1 .. &1 loop
new   2:        for i in 1 .. 1000 loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.55
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> drop table t;

Table dropped.

Elapsed: 00:00:00.34
ops$tkyte_at_DEV8I.WORLD> create table t
  2 (
  3 x varchar(25)

  4     constraint x_must_be_ALPHA
  5     check ( replace(
  6                  translate(
  7           upper(x),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad('x',26,'x')
  8                           ), 'x','') is null )

  9 );

Table created.

Elapsed: 00:00:00.09
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> begin

  2          for i in 1 .. &1 loop
  3                  insert into t values ( rpad( 'x', 25, 'x' ) );
  4          end loop;

  5 end;
  6 /

old   2:        for i in 1 .. &1 loop
new   2:        for i in 1 .. 1000 loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.55
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> drop table t;

Table dropped.

Elapsed: 00:00:00.38
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> create table t
  2 (
  3 x varchar(25)
  4 )
  5 /

Table created.

Elapsed: 00:00:00.11

ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> create or replace trigger T_Trigger

  2 after insert on t for each row
  3 begin

  4          if ( replace(
  5                  translate(
  6           upper(:new.x),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',rpad
('x',26,'x')
  7                           ), 'x','') is not null )
  8          then
  9                  raise_application_error(-20001, 'error' );
 10          end if;

 11 end;
 12 /

Trigger created.

Elapsed: 00:00:00.10
ops$tkyte_at_DEV8I.WORLD>
ops$tkyte_at_DEV8I.WORLD> begin

  2          for i in 1 .. &1 loop
  3                  insert into t values ( rpad( 'x', 25, 'x' ) );
  4          end loop;

  5 end;
  6 /

old   2:        for i in 1 .. &1 loop
new   2:        for i in 1 .. 1000 loop

PL/SQL procedure successfully completed.

Elapsed: 00:00:03.09
ops$tkyte_at_DEV8I.WORLD>

--
Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
Howtos and such: http://osi.oracle.com/~tkyte/index.html
Oracle Magazine: http://www.oracle.com/oramag
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Thu Jun 15 2000 - 00:00:00 CDT