Phone Format in T-SQL using CHECK

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

Valid PhoneNo entries

Invalid PhoneNo entries

Please feel free to correct me by commenting your feedback and suggestions.

Email Format in T-SQL using CHECK

Many times we come across a requirement which requires us to implement certain methodology to check for valid EmailID entries. The following article will try to achieve the same using CHECK constraint in T-SQL. 

---

We have used the following understanding of valid EmailID's: 

• First letter should be alphabet

• Length of Email part before @ should be <= 15 

• @ should appear only once

• Dot (.) should appear only once and after @ and should have at least one alphabet between them.        

• After dot only alphabets should appear with length limited to 1 to 3 characters

• Should not have any special characters and only AlphaNumeric

  

  CHECK constraint to check for valid EmailID entries in table

   

  
  

  

  Examples of invalid Inserts

  
  

  
  

  

  Examples of valid Insert

Please provide your feedback and suggestions by commenting

T-SQL 2012 Interview questions

Q: Given two tables T1 and T2 with same structure. If T1 has 20 records and T2 has 35 records, then the Full Join should result in how many records?

A: 50 records.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Q: What is difference between Equi-Join and NonEqui-Join?

A: If all columns in the ON clause are matched on equality ( using operator = ) the join is called "Equi-Join". If one or more columns are using an inequality comparison (using operators like <>, >, >=, <, <=, != ) the join is called "Nonequi-Join".

Q: Given table Country with one column with values India, Australia and England in specified order. Write a T-SQL query to display knockout matches between teams in the specified order: 

India vs Australia

India vs England

England vs Australia

Q: Given table T1 with ID column and row values 3, 10, 19, 20. Find missing values starting with the minimum and maximum from table.

Please leave your suggestions and feedback by commenting