Email Validation in SQL using Pattern Matching as a case study

Rajnilari2015
Posted by in Sql Server category on for Beginner level | Points: 250 | Views : 1111 red flag

This post is just to bring up some alternate uses of the 'LIKE' operator in SQL, probably as a possible alternative to regex - at least for a couple of scenarios.

Pattern matching is an integral part of our day to day programming. For example, let us consider validating an email address by SQL.


 Download source code for Email Validation in SQL using Pattern Matching as a case study

Recommendation
Read Extract numbers from string using T-SQL before this article.

Introduction

This post is just to bring up some alternate uses of the 'LIKE' operator in SQL, probably as a possible alternative to regex - at least for a couple of scenarios.

Pattern matching is an integral part of our day to day programming. For example, let us consider validating an email address by SQL

Environment Setup

To demonstrate the point, let's create a table and insert some values

 
DECLARE @T TABLE(EMAILS VARCHAR(50))

INSERT @T(EMAILS)
SELECT '123abc@aqw.com' UNION ALL
SELECT 'A!B#C@GMAIL.COM' UNION ALL
SELECT 'A+BC@E.COM' UNION ALL
SELECT 'A@123.COM' UNION ALL
SELECT 'ABC#GMAIL.COM' UNION ALL
SELECT 'ABC@!.COM' UNION ALL
SELECT 'ABC@G123.COM' UNION ALL
SELECT 'ABC@GMAIL.COM#' UNION ALL
SELECT 'ABC123@GMAIL123.COM' UNION ALL
SELECT 'ABNC@34.COM' UNION ALL
SELECT 'ANC@E.COM##' UNION ALL
SELECT 'ASD@W.B.K.I#' UNION ALL
SELECT 'ASN@WE.COM(' UNION ALL
SELECT 'B@G@H.COM' UNION ALL
SELECT 'DF@FGH V .C' UNION ALL
SELECT 'FRT@AW.678' UNION ALL
SELECT 'FRTY@1Q.COM' UNION ALL
SELECT 'JK@G MIL.COM' UNION ALL
SELECT 'M B @G.COM' UNION ALL
SELECT 'mail2nil_9993@gmail.com' UNION ALL
SELECT 'mail2nil678#gmail.com' UNION ALL
SELECT 'mail2nil@gmail. ' UNION ALL
SELECT 'mnop@gmail.com' UNION ALL
SELECT 'xyz@something$com' UNION ALL
SELECT 'mail2nil5678@test-abc.com' UNION ALL
SELECT 'mail2us9652@wrong' UNION ALL
SELECT 'mail2user956@yahoo.co.in' UNION ALL
SELECT 'NNN@GG' UNION ALL
SELECT 'YOTRIYO@WW.YO' UNION ALL
SELECT 'YYTYT@566DDDD.COM'

SELECT * FROM @T

Validations Imposed

The constraints imposed for this validation are

  1. The personal component i.e. the one that comes before the @ sign should have
    1. The starting character must start with a alphabetic character
    2. It can be of any length but with alpha-numeric character with no space
  2. The location component i.e. the one that comes after the @ sign should have

    Only alphabetic characters which can be of any length and can have only the following pattern @[Host Name].[Domain Name]{.[Domain Name]…}

Using the code

Now, let us see how to use the LIKE operator, to obtain the email addresses.

SELECT EMAILS
FROM @T
WHERE EMAILS LIKE '[A-Z][A-Z]%[@][^0-9][A-Z]%[.][A-Z]%[A-Z]'
AND NOT ( EMAILS LIKE '%[@][A-Z]%[0-9]%' )

Explanation

  • It is only a pattern matching program where the personal components are validated
  • First [A-Z] meaning that the first character will only be a alphabetic character followed by any characters with no space [A-Z0-9]%
  • Next location components are validated like [@][^0-9][A-Z]%[.][A-Z]%[A-Z]. It is the same as above and henceforth no need of extra explanation.
  • Lastly, we should not accept the string in the location component whose patterns are a belnd of alphanumeric. It has been taken care of by this snippet
    AND NOT ( EMAILS LIKE '%[@][A-Z]%[0-9]%' )

The output is as expected

EMAILS
mail2nil_9993@gmail.com
mnop@gmail.com
mail2nil5678@test-abc.com
mail2user956@yahoo.co.in
YOTRIYO@WW.YO

Usage

The use of pattern matching exists in variety. Some glimpse

  • Finding a valid SSN
  • Finding a valid Passport Number
  • Finding a valid Adhar Card Number
  • Searching a word in a string
  • Searching alphabets and numbers from a conglomeration of alphanumeric string
  • Searching a date pattern in a string
are a few among the many

Reference

SQL LIKE Operator

Conclusion

In this article we have seen the importance of pattern matching with Email validation as a case study that we have achieved by using the LIKE operator.Thanks for reading.Zipped file is attached herewith.

Recommendation
Read Create Custom Templates in SQL Server 2012 after this article.
Page copy protected against web site content infringement by Copyscape

About the Author

Rajnilari2015
Full Name: Niladri Biswas (RNA Team)
Member Level: Platinum
Member Status: Member,Microsoft_MVP,MVP
Member Since: 3/17/2015 2:41:06 AM
Country: India
-- Thanks & Regards, RNA Team


Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)