Constraint for phone number in SQL Server

Question

Constraint for phone number to be of 7 digits. How to check if it is of 7 digits in SQL Server?

CREATE TABLE Customer
(
    C_ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
    C_Name VARCHAR(255) NOT NULL,
    Phone INT 
);

Answer 1

Do not store phone numbers as integers. Some valid numbers, for instance, could start with a 0 -- if not today, perhaps in the future. To do the validation check, you can use like:

CREATE TABLE Customer (
    C_ID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    C_Name VARCHAR(255) NOT NULL,
    Phone CHAR(7), -- you might not want to have such a precise length
    CONSTRAINT chk_phone CHECK (phone not like '%[^0-9]%') -- check that no number is not a digit 
);

Alternatively, you could write:

CONSTRAINT chk_phone CHECK (phone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]') -- check that no number is not a digit 

Answer 2

Let's say you have a student table. In order to check the contact length, the following code works:

ALTER TABLE Student ADD CONSTRAINT SD_CHK check(contact like '[0-9]*10');

Hope this may help you

Answer 3

it worked with Phone VARCHAR CHECK(DATALENGTH(Phone)=7)

Answer 4

You'd better store phone number as varchar2 or char type. In Oracle, you can check the validation of phone number by Regular Expression:

CREATE TABLE Customer
(
    C_ID INT NOT NULL PRIMARY KEY,
    C_Name VARCHAR(255) NOT NULL,
    Phone char(10),
    CONSTRAINT valid_phone_number 
    CHECK (REGEXP_LIKE(p_number, '^0\d{9}|\d{10}$'))
);

'^0\d{9}|\d{10}$' means phone number must start with digit 0, then followed by 9 or 10 digits (i.e 01646947314 (11 digits) or 0123456789 (10 digits) is VALID, and 123456789 or 0123456 is not valid). You can modify this code to 7 digits by dropping "|" in regular expression and change to d{7}. Hope this help you (or someone else's having the similar problem)!

Answer 5

According to the NANP, seven-digit phone numbers in the US are NXX-XXXX, where N is 2-9 and X is 0-9. Additionally the second and third numbers cannot both be 1. Assuming you want to store only real NANP phone numbers, you could use the following constraint:

ALTER TABLE Customer
ADD CONSTRAINT CHK_Phone_valid
CHECK (Phone >= 2000000 AND Phone <= 9999999 and Phone / 10000 % 100 <> 11)

If you wanted to add an area code, you would need to use a bigint and different/additional constraints. Additionally, any sort in internal phone numbers, extensions, area codes, international phone numbers, etc., will have different requirements.

As someone who frequently deals with a large number of (exclusively NANP) phone numbers in databases, I do find that keeping them in a number format is ideal, as it is faster, smaller, and has the added benefit of preventing various formatting problems by default. Though for most users, this is probably all moot.

Answer 6

Necromancing.
If you really really just want a constraint that enforces exactly 7 digits, then you can do this:

DECLARE @phone TABLE 
(
     phone_id int NOT NULL PRIMARY KEY 
    ,phone_name nvarchar(255) NOT NULL 
    ,phone_number int NULL CHECK 
    (
        phone_number >= 1000000 -- or > 999999
        AND 
        phone_number <= 9999999 -- or < 10000000    
    ) 
);



INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 1 AS phone_id, N'Person 1' AS pn, '1000000' AS phone_number; 

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 2 AS phone_id, N'Person 2' AS pn, '9999999' AS phone_number; 


INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 3 AS phone_id, N'Person 3' AS pn, '+1000000' AS phone_number; 

Now, if you ever need the preceding zeroes, you can just do:

SELECT RIGHT(('0000000') + CAST(phone_number as varchar(32)), 7) AS number FROM @phone 

However, if you just end up here from a google-search looking for a constraint for a phone-number in general...

then, I've just been thinking about what the correct constraint for a phone number would be.

Looking at questions 723587, I was able to determine that the maximum-length a phone-number can have is 31 digits.

Given that we have the need to send SMS, which means it has to be a valid-number to send, I figured the best "constraint" to prevent people from littering the field with garbage is to declare it as decimal(31, 0). You can also put a check-constraint in there to enfore a minimum-length for the phone number (minimum-number). Also, you need to check that the number is not less than zero.

If you absolutely want to do it with varchar, then looking at Gordon Linoff's post, I determine it needs to allow a + sign at the start of the number, so ultimately the field-type is varchar(32).

If you don't need the +, then making the field varchar is superfluous.

And one constraint that you could put on a varchar would be

DECLARE @phone TABLE 
(
     phone_id int NOT NULL PRIMARY KEY 
    ,phone_name nvarchar(255) NOT NULL
    -- ,phone_number varchar(31) NULL CHECK (phone_number NOT LIKE '%[^0-9]%') -- result of check may not be false
    -- ,phone_number varchar(31) NULL CHECK (phone_number NOT LIKE '%[^0-9+]%') -- result of check may not be false
    ,phone_number varchar(32) NULL CHECK 
    (
        (
            phone_number NOT LIKE '%[^0-9]%'
            OR 
            (
                SUBSTRING(phone_number, 1, 1) = '+' 
                AND 
                SUBSTRING(phone_number, 2, 35) NOT LIKE '%[^0-9]%'
            )
        )
        AND phone_number <> '' 
    ) 

    --CONSTRAINT chk_phone CHECK (phone_number NOT LIKE '%[^0-9]%') -- check that no number is not a digit 
);

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 1 AS phone_id, N'Person 1' AS pn, '123' AS phone_number; 

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 2 AS phone_id, N'Person 2' AS pn, '+123' AS phone_number; 

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 3 AS phone_id, N'Person 3' AS pn, '++123' AS phone_number; 

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 4 AS phone_id, N'Person 3' AS pn, '' AS phone_number; 

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 5 AS phone_id, N'Person 3' AS pn, NULL AS phone_number; 

Note that this allows NULL-values.
To disallow them, change the field-type from NULL to NOT NULL.

Also, note that you should always differenciate between the data, and the actual display value.

I would thus argue that the best way to store a phone number is this:

DECLARE @phone TABLE 
(
     phone_id int NOT NULL PRIMARY KEY 
    ,phone_name nvarchar(255) NOT NULL 
    ,phone_number decimal(31,0) NULL CHECK 
    (
        -- phone_number > 0 AND 
        phone_number > 99
    ) 
);



INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 1 AS phone_id, N'Person 1' AS pn, '112' AS phone_number; 

INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 2 AS phone_id, N'Person 2' AS pn, '+12126879970' AS phone_number; 

-- INSERT INTO @phone ( phone_id, phone_name, phone_number )
-- SELECT 3 AS phone_id, N'Person 3' AS pn, '-12126879970' AS phone_number; 

-- INSERT INTO @phone ( phone_id, phone_name, phone_number )
-- SELECT 4 AS phone_id, N'Person 4' AS pn, '++12126879970' AS phone_number; 

-- INSERT INTO @phone ( phone_id, phone_name, phone_number )
-- SELECT 5 AS phone_id, N'Person 5' AS pn, '+' AS phone_number; 

-- INSERT INTO @phone ( phone_id, phone_name, phone_number )
-- SELECT 6 AS phone_id, N'Person 6' AS pn, '0' AS phone_number; 

-- INSERT INTO @phone ( phone_id, phone_name, phone_number )
-- SELECT 7 AS phone_id, N'Person 7' AS pn, '+0' AS phone_number; 


INSERT INTO @phone ( phone_id, phone_name, phone_number )
SELECT 8 AS phone_id, N'Person 8' AS pn, '100.1' AS phone_number; 


SELECT * FROM @phone 

Note that due to +SOMETHING being a valid number, you can also have a + in there.
And values like 0.0 get truncated to 0.
One caveat: this allows values such as '100.1' etc.

If you have a formatted phone-number as input that uses spaces and/or dashes, just do REPLACE(REPLACE('YOUR_PHONE_NUMBER', ' ', ''), '-', ''), and the constraint will help prevent any unwanted formatting-characters that are not caught by the replace from entering the DB (such as braces).