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.
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
-
The personal component i.e. the one that comes before the @ sign should have
- The starting character must start with a alphabetic character
- It can be of any length but with alpha-numeric character with no space
-
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
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.