Format Phone Number in a Table in Oracle

Question

I am new to SQL as well as Oracle. I was playing with Oracle. I tried to have a constraint on phone number so it only allows the phone number to be inserted in a specific way.

Below is my table:

CREATE TABLE FIRSTTRY(
USERID CHAR(9) NOT NULL,
USERNAME VARCHAR(20) NOT NULL,
EMAILADDRESS VARCHAR(40),
PHONENUMBER VARCHAR(12),
PROFILEPICTURE BLOB,
PRIMARY KEY(USERID)
);

I put the constraint on phone number like this:

ALTER TABLE FIRST_TRY
ADD CONSTRAINT FIRSTTRY_PHONENUMBER CHECK 
(PHONENUMBER LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9]' 
OR PHONENUMBER LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' 
OR PHONENUMBER LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9][0-9]');

I am inserting the values to the table like this:

INSERT INTO FIRST_TRY VALUES (1,'ABCD','[email protected]','0504562893',NULL);

But it gives me an error: Here is the error:

02290. 00000 -  "check constraint (%s.%s) violated"
*Cause:    The values being inserted do not satisfy the named check

*Action:   do not insert values that violate the constraint.

Any ideas, suggestions?

Answer 1

ALTER TABLE FIRST_TRY
ADD CONSTRAINT FIRST_TRY_PHONENUMBER 
CHECK (regexp_like (PHONENUMBER,'^(\d{3}-\d{3}-?\d{4}|\d{10})$'))

---

INSERT INTO FIRST_TRY VALUES (1,'ABCD','[email protected]','0504562893'  ,NULL);
INSERT INTO FIRST_TRY VALUES (2,'ABCD','[email protected]','050-4562893' ,NULL);
INSERT INTO FIRST_TRY VALUES (3,'ABCD','[email protected]','050-456-2893',NULL);