CODEWITHSUNDEEP
sqlregexoracle-databaseconstraintsalphanumeric
Dkarthe97
Dkarthe97

Reputation: 21

how to define a constraint for column to have alphanumeric values in oracle?

I need to create a table which has a column. the entries should be alphanumeric. how can i define a constraint for this situation I tried using, 

create table bankAccount  
(  
accNo varchar2(15) check (accNo like '%[A-Za-Z0-9]%')  
);

but during input no data is stored and no entries are created.

Upvotes: 2

Views: 2837

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

You would use regexp_like():

accNo varchar2(15) check (regexp_like(accNo, '^[A-Za-z0-9]+$'))

Upvotes: 1

David Faber
David Faber

Reputation: 12495

You would use REGEXP_LIKE() but if all the characters have to be alphanumeric then you need start and end anchors:

CREATE TABLE bankaccount
( accno VARCHAR2(15) CHECK ( REGEXP_LIKE(accno, '^[A-Za-z0-9]+$') ) );

(Note that you can also use the [[:alnum:]] POSIX character class instead of [A-Za-z0-9].)

If you want the account numbers to be exactly 15 characters, then you might want something like this:

CREATE TABLE bankaccount
( accno VARCHAR2(15) CHECK ( REGEXP_LIKE(accno, '^[A-Za-z0-9]{15}$') ) );

And if you want only uppercase letters:

CREATE TABLE bankaccount
( accno VARCHAR2(15) CHECK ( REGEXP_LIKE(accno, '^[A-Z0-9]+$') ) );

Hope this helps.

Upvotes: 4

Related Questions