Email Validation in SQL using Pattern Matching as a case study

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

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.