Kamil Kamili Kamil Kamili - 21 days ago 5
SQL Question

Format Phone Number in a Table in Oracle

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','ABCD@YAHOO.COM','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
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','ABCD@YAHOO.COM','0504562893'  ,NULL);
INSERT INTO FIRST_TRY VALUES (2,'ABCD','ABCD@YAHOO.COM','050-4562893' ,NULL);
INSERT INTO FIRST_TRY VALUES (3,'ABCD','ABCD@YAHOO.COM','050-456-2893',NULL);