We will try to write a T-SQL query to allow only valid phone number entries in table column using CHECK constraint.
For this article, we have used the following link to get the valid phone number formats in India.
We have zeroed on following as valid formats:
Landlines like: 080-1234567, 0831-123456
Mobiles like: +91-1234567890, 01234567890
Toll Free like: 1-800-123456
Service numbers like: 100, 101
The following query creates a temporary table with PhoneNoType and PhoneNo varchar columns. Based on the PhoneNoType, i.e. Landline or Mobile or Toll Free or Service, PhoneNo format will be required to be checked and inserted into table, if valid.
The logic is to compare the entry in PhoneNo column with set valid possibilities based on PhoneNoType column.
Create table with named CHECK constraint on PhoneNo column
Create table with named CHECK constraint on PhoneNo column
Valid PhoneNo entries |
Invalid PhoneNo entries |
No comments:
Post a Comment