T-SQL: checking for email format

by

I tested the following query with many different wrong and valid email addresses. It should do the job. 

IF (
     CHARINDEX(' ',LTRIM(RTRIM(@email_address))) = 0 
AND  LEFT(LTRIM(@email_address),1) <> '@' 
AND  RIGHT(RTRIM(@email_address),1) <> '.' 
AND  CHARINDEX('.',@email_address ,CHARINDEX('@',@email_address)) - CHARINDEX('@',@email_address ) > 1 
AND  LEN(LTRIM(RTRIM(@email_address ))) - LEN(REPLACE(LTRIM(RTRIM(@email_address)),'@','')) = 1 
AND  CHARINDEX('.',REVERSE(LTRIM(RTRIM(@email_address)))) >= 3 
AND  (CHARINDEX('.@',@email_address ) = 0 AND CHARINDEX('..',@email_address ) = 0)
)
   print 'valid email address'
ELSE
   print 'not valid'

It checks these conditions:

  • No embedded spaces
  • ‘@’ can’t be the first character of an email address
  • ‘.’ can’t be the last character of an email address
  • There must be a ‘.’ somewhere after ‘@’
  • the ‘@’ sign is allowed
  • Domain name should end with at least 2 character extension
  • can’t have patterns like ‘.@’ and ‘..’

More Related Contents:

  1. Is it possible to write single query on two table which are not connected to each other?
  2. How to create a specific SQL Server stored procedure? [closed]
  3. SQL Server dynamic PIVOT query?
  4. SQL Server – Best way to get identity of inserted row?
  5. Should I use != or for not equal in T-SQL?
  6. How to update two tables in one statement in SQL Server 2005?
  7. SQL Server Process Queue Race Condition
  8. How should I pass a table name into a stored proc?
  9. Is the NOLOCK (Sql Server hint) bad practice?
  10. Creating an index on a table variable
  11. SQL – Subtracting a depleting value from rows
  12. OR is not supported with CASE Statement in SQL Server
  13. How to set variable from a SQL query?
  14. SELECT FOR UPDATE with SQL Server
  15. Compare DATETIME and DATE ignoring time portion
  16. T-SQL: Selecting Column Based on MAX(Other Column)
  17. How to group by month from Date field using sql
  18. unresolved reference to object [INFORMATION_SCHEMA].[TABLES]
  19. SQL dot notation
  20. Split function in SQL Server 2008
  21. T-SQL How to create tables dynamically in stored procedures?
  22. What is the equivalent of ‘describe table’ in SQL Server?
  23. OPENXML with xmlns:dt
  24. How can I group by date time column without taking time into consideration
  25. Update query using Subquery in Sql Server
  26. SQL Server SELECT INTO @variable?
  27. WITH CHECK ADD CONSTRAINT followed by CHECK CONSTRAINT vs. ADD CONSTRAINT
  28. Select a column if other column is null
  29. Date serial in SQL?
  30. SQL Server: +(unary) operator on non-numeric Strings

Leave a Comment