Home 2018 December SQL Puzzle: SQL Advance Query – Do basic validation of Email Address

SQL Puzzle: SQL Advance Query – Do basic validation of Email Address

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

Check the below input data and expected output and perform the basic Email Validation using simple LIKE predicate.

Input Data:

Expected Output:

Create a table with sample data:

Solution:

Please try the different solution for this puzzle and share it via comment...

Anvesh Patel
SQL Server: If a Column Exists in a Table, don't add itSQL Server: If Table Exists in a Database, don't perform any action
Comments: 3
  1. HIC
    April 7, 2019 at 6:56 am

    select email,
    case when email like ‘%_@_%._%’ then 1 else 0 end
    from emails

    ReplyCancel
    • Sumit Salunkhe
      April 12, 2019 at 6:09 am

      select emailname ,CASE WHEN emailname like ‘%[a-z,0-9]@[a-z]%.com’ then ‘true’ else ‘false ‘ end as True_Email
      from tbl_emails

      ReplyCancel
  2. Marcel Hofman
    April 10, 2019 at 8:31 am

    Following the criteria for a correct email address
    select emailname
    ,
    CASE WHEN
    RIGHT(emailname, 4) = ‘.com’
    AND emailname LIKE ‘%@%’
    AND patindex(‘[a-zA-Z0-9]%’, emailname) 0
    AND patindex(‘[-]%’, emailname) 1
    AND patindex(‘[_]%’, emailname) 1
    AND patindex(‘[.]%’, emailname) 1
    AND patindex(‘[0-9]%’, emailname) 1
    AND patindex(‘[0-9]%’, emailname) 1
    AND emailname NOT LIKE ‘%%’
    AND emailname NOT LIKE ‘%(%’
    AND emailname NOT LIKE ‘%)%’
    AND emailname NOT LIKE ‘%[%’
    AND emailname NOT LIKE ‘%]%’
    AND emailname NOT LIKE ‘%;%’
    AND emailname NOT LIKE ‘%,%’
    AND emailname NOT LIKE ‘%\%’
    AND emailname NOT LIKE ‘%@%@%’
    THEN ‘true’ ELSE ‘false’ END AS IsCorrectEmail
    FROM tbl_emails

    ReplyCancel

Leave a Reply

CAPTCHA

*

Anvesh Patel
Anvesh Patel

Database Engineer

December 17, 2018 3 Comments SQL Puzzle, , , , , , , , , , ,