CODEWITHSUNDEEP
sqlregexoracle-databaseplsql
Wahid Masud
Wahid Masud

Reputation: 1093

oracle specific column accepts a specific format of character

In oracle database I have a table 'emp_table' that has a column 'eTIN' which is NVARCHAR2(12). I want to modify it to accept only 0-9 as character. I dont want to modify is as number type. So that next time I will have scope to modify it further. How to achieve this?

Upvotes: 1

Views: 41

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172458

You can try to use the CHECK constraint like this:

CHECK (regexp_like(myCol,'^[[:digit:]]+$'))

Something like

SQL> CREATE TABLE myTable(
  2  myCol VARCHAR2(12),
  3  CONSTRAINT constraint_name CHECK (regexp_like(myCol,'^[[:digit:]]+$'))
  4  )
  5  /

And if the table is already created then simply ALTER the table like this:

ALTER TABLE myTable ADD CONSTRAINT constraint_name CHECK (regexp_like(myCol,'^[[:digit:]]+$'))

Upvotes: 4

Related Questions