CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Thiyagu ATR
Thiyagu ATR

Reputation: 2264

how to limit a column in oracle to support only character?

i need to create a table which has number of columns in which one column is varchar2 type.Now the problem is i need to set that particular column should support only alphabets. I've tried this code but this didn't get work can anybody help me!
thanks in advance!

CREATE TABLE test_1
(
  test_id number,
  test_name varchar(50),
  CONSTRAINT name_test
  CHECK (test_name where regexp_like(test_name,'^([aA-zZ])+$'))
);

Upvotes: 2

Views: 187

Answers (3)

Art
Art

Reputation: 5782

http://www.dba-oracle.com/t_regular_expressions_constraints_updates_columns.htm

...CHECK (regexp_like(test_name,'^[[:alpha:]]+$') )

Upvotes: 0

Florin Ghita
Florin Ghita

Reputation: 17643

You are close, just use the function(which returns true/false).

CREATE TABLE z_test_1
(
  test_id number,
  test_name varchar(50),
  CONSTRAINT name_test
  CHECK (regexp_like(test_name,'^([aA-zZ])+$'))
);

Upvotes: 0

Raad
Raad

Reputation: 4648

Try:

CREATE TABLE test_1
(
  test_id number,
  test_name varchar(50),
  CONSTRAINT
    name_test
    CHECK (
      regexp_like(test_name,'^([aA-zZ])+$')
    )
);

Upvotes: 1

Related Questions