Clever Geek Handbook

How to allow emails only in VARCHAR2 column (Oracle SQL)

I have a column "last name" in my Oracle SQL database.

Test CREATE TABLE (id VARCHAR2 (3) PRIMARY KEY, last name VARCHAR2 (10));

What should be added in the query above to allow only alphabetic characters to be inserted in "last name"?

+3


source to share


1 answer


You need to have it CHECK constraint

in your table.

Enough CHECK (regexp_like(surname,'^[[:alpha:]]+$'))

. Where [[:alpha:]]

is the alphabetical character class. Thus, only alphabets are considered.

Let's look at a test case -

SQL> CREATE TABLE TEST(
  2  surname VARCHAR2(10),
  3  CONSTRAINT constraint_name CHECK (regexp_like(surname,'^[[:alpha:]]+$'))
  4  )
  5  /

Table created.

SQL>
SQL> INSERT INTO TEST values ('LALIT')
  2  /

1 row created.

SQL>
SQL> INSERT INTO TEST values ('123')
  2  /
INSERT INTO TEST values ('123')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.CONSTRAINT_NAME) violated


SQL>


So far this works well. Let the values ​​be checked Alphanumeric

.

SQL> INSERT INTO TEST VALUES ('LALIT123')
  2  /
INSERT INTO TEST VALUES ('LALIT123')
*
ERROR at line 1:
ORA-02290: check constraint (LALIT.CONSTRAINT_NAME) violated


SQL>
SQL> SELECT * FROM TEST
  2  /

SURNAME
----------
LALIT

SQL>

So, the limitation CHECK

ONLY allows alphabets

.

+4


source






More articles:


All Articles