Re: How to allow only Alphanumeric for varchar?

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/06/14
Message-ID: <8i8qaj$f75$1@nnrp1.deja.com>#1/1

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.

Received on Wed Jun 14 2000 - 00:00:00 CDT