Re: How to allow only Alphanumeric for varchar?

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)
>
> --
> 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.

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