TSQL Email Validation (without regex)

by

Very basic would be:

SELECT
  EmailAddress, 
  CASE WHEN EmailAddress LIKE '%_@_%_.__%' 
            AND EmailAddress NOT LIKE '%[any obviously invalid characters]%' 
  THEN 'Could be' 
  ELSE 'Nope' 
  END Validates
FROM 
  Table

This matches everything with an @ in the middle, preceded by at least one character, followed by at least two, a dot and at least two for the TLD.

You can write more LIKE patterns that do more specific things, but you will never be able to match everything that could be an e-mail address while not letting slip through things that are not. Even with regular expressions you have a hard time doing it right. Additionally, even matching according to the very letters of the RFC matches address constructs that will not be accepted/used by most emailing systems.

Doing this on the database level is maybe the wrong approach anyway, so a basic sanity check as indicated above may be the best you can get performance-wise, and doing it in an application will provide you with far greater flexibility.

More Related Contents:

  1. SQL Server – error when creating stored procedure
  2. How can foreign key constraints be temporarily disabled using T-SQL?
  3. Pad a string with leading zeros so it’s 3 characters long in SQL Server 2008
  4. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  5. Maximum size of a varchar(max) variable
  6. best way to convert and validate a date string
  7. How to get a date in YYYY-MM-DD format from a TSQL datetime field?
  8. How do I format a number with commas in T-SQL?
  9. How to set a default value for an existing column
  10. Need a datetime column in SQL Server that automatically updates when the record is modified
  11. Is there a way to simplify a NULL compare of 2 values
  12. TSQL Try / Catch within Transaction or vice versa?
  13. What is the maximum number of characters that nvarchar(MAX) will hold?
  14. How do I find a stored procedure containing ?
  15. Query to list all stored procedures
  16. SQL Server: Index columns used in like?
  17. Recursive sum in tree structure
  18. SQL Server – Auto-incrementation that allows UPDATE statements
  19. Get ROWS as COLUMNS (SQL Server dynamic PIVOT query)
  20. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  21. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  22. FOR XML PATH(”): Escaping “special” characters
  23. Cannot bulk load. Operating system error code 5 (Access is denied.)
  24. Instead of trigger in SQL Server loses SCOPE_IDENTITY?
  25. Does query plan optimizer works well with joined/filtered table-valued functions?
  26. SQL Server error “Implicit conversion of because the collation of the value is unresolved due to a collation conflict.”
  27. SQL SERVER – Understanding how MIN(text) works
  28. Pivot multiple columns based on one column in SQL Server
  29. @@ERROR and/or TRY – CATCH
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment