Constraint for Phone Number in SQL Server

Constraint for phone number in SQL Server

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 

Sql phone number constraint

alter table dbo.Test
add constraint UQ_Phone unique (c_Phone)


alter table dbo.Test
add constraint CC_Phone  check
(
    c_Phone not like '%[^0-9]%'and 
    ((c_Phone like '03%' and len(c_Phone) = 11) or 
    (c_Phone not like '_3%' and len(c_Phone) = 10))
)

Check constraint for phone number format

Problems with your code:

• to check a value against a regex, you need to use the REGEXP operator instead of LIKE
• the opening and closing parenthese need to be escaped (this requires two backslashes)

Also, you can use quantifiers to avoid repeating [0-9] again and again.

Consider:

CHECK (phone REGEXP '\\(0[0-9]\\)[0-9]{3}-[0-9]{4}')

Demo on DB Fiddle

How to insert check constraint for phone number in mysql?

Use simpler construct for digits, [0-9], and escape plus in a proper way, or i- if in doubt - put it into a bracket expression:

'^[+][0-9]{12}$'

How to add validation on a phone number?

Here is what I have put together:

check ( (length(phone) = 11 and
       phone like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]') 
       or
       (regexp_count(replace(phone,'+',''), '[0-9]') = 12 and
       phone like '+%' and
       regexp_like((reverse(phone)), '^[0-9]{12}'))
       or
       (regexp_count(replace(phone,'-',''), '[0-9]') = 11  and 
       (regexp_like((phone), '^[0-9]{4}') and
       regexp_like((reverse(phone)), '^[0-9]{4}')) or 
       (regexp_like((phone), '^[0-9]{5}') and
       regexp_like((reverse(phone)), '^[0-9]{3}')))
       )

By using and and or operators I have created 3 different conditions to be check:

• In first I check the length of the column phone and if it is 11 characters long then also check if every character of this 11 is numeric.
• Second condition checks if the string is 12 characters long but without the character +. If it is then check if it starts with + and check does it ends with 12 numbers.
• Third is first checking the length of the string without character - and then it checks does the string starts with 4 numbers and does it end with 3 numbers.

how to write a query with constraint of 'phone number has 4 consecutive digits between 0 and 2 inclusive' in sql

Right now you check, if the phone number consists only of four digits between 0 and 2. You need to add wildcards if there can be more characters in the phone number.

• If the four digits can appear anywhere in the string use:

  CHECK (phone LIKE '%[0-2][0-2][0-2][0-2]%')
  

• If they can only appear at the beginning use:

  CHECK (phone LIKE '[0-2][0-2][0-2][0-2]%')
  

• If they can only appear at the end use:

  CHECK (phone LIKE '%[0-2][0-2][0-2][0-2]')
  

Set the constraint to start with 0 then 9 only and contain 11 digits?

The first issue you've out there is that you're using the wrong datatype for phone numbers, thus you need to use a proper on which is in your case CHAR(11)/VARCHAR(11).

Then you need to create the constraint as the following:

CREATE TABLE MyTable (
  ContactNo varchar(255)
  CONSTRAINT CK_ContactNo2 
  CHECK (ContactNo LIKE '09[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')                 
); 



INSERT INTO MyTable(ContactNo) VALUES ('11111') -- Fail
INSERT INTO MyTable(ContactNo) VALUES ('09123456789')-- Succeed

and don't forgot to drop the previous one that if you're no re-creating your table.

---

Related Topics