SQL Email Verification Function using Regex

Question

I am trying to create a function in SQL Server 2005 to check to see if an email is in a valid format with regular expressions.

Here is what I have so far:

CREATE FUNCTION isValidEmailFormat
(
    @Email varchar(100)
)
RETURNS bit
AS
BEGIN
   DECLARE @Result bit

   SET @Result = (SELECT CASE
                     WHEN @Email LIKE '%[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+  (com|org|edu|nz|au])%' 
                       THEN 1
                       ELSE 0
                  END AS Valid)
   RETURN @Result
END

Am I doing something wrong with my regular expression? Or do I have to do something more to compare a varchar to a regular expression?

-Edit-

Right now, whatever string I put in returns a 0, even if the email format itself is correct.

Womankind

I don't think this is your problem, but there's an extra ] after au. – Steverson

@acheong87 That wasn't the issue. Thank you, though because that could help me later down the road when I find a solution. – Womankind

Answer 1

The short answer is that no, it cannot be done. The syntax for LIKE is not the same as (and way less powerful than) regular expressions. See also SQL Server Regular expressions in T-SQL

But you can make the hop to .Net and do the matching there. You can instantiate a VBScript.RegExp inside T-SQL using sp_OACreate and use that.

CREATE FUNCTION dbo.isValidEmailFormat
(
    @Email varchar(100)
)
RETURNS bit
AS
BEGIN
    DECLARE @pattern varchar(4000)
    SET @pattern = '[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|nz|au)'
    DECLARE @Result bit

    DECLARE @objRegexExp INT
    EXEC sp_OACreate 'VBScript.RegExp', @objRegexExp OUT

    EXEC sp_OASetProperty @objRegexExp, 'Pattern', @pattern
    EXEC sp_OASetProperty @objRegexExp, 'IgnoreCase', 1
    EXEC sp_OASetProperty @objRegexExp, 'MultiLine', 0
    EXEC sp_OASetProperty @objRegexExp, 'Global', false
    EXEC sp_OASetProperty @objRegexExp, 'CultureInvariant', true

    EXEC sp_OAMethod @objRegexExp, 'Test', @Result OUT, @Email

    EXEC sp_OADestroy @objRegexExp

    RETURN @Result
END

Do take a peek at Regex email verification error - using JavaScript to see if you want to be a bit less restrictive on what characters are allowed.

Rodriquez

Thank you @flup. I was trying to avoid writing a program, but I guess I will have to. I have a program that uses this, I just didn't want to compare customer data in two different places. – Womankind

I'm not sure if I'd call it a program. You can instantiate the regexp object INSIDE SQL Server and call its methods. I'll add the code to my answer. – Rodriquez

Thank you for the update! The only error in your code is that you need to SET @pattern = '[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+ (com|org|edu|nz|au])' – Womankind

Also, was able to create the function, however, I am still getting a result of 0. My test was '[email protected]' – Womankind

Need to lose a space before the (com – Rodriquez

Answer 2

I used @flup 's answer and updated the RegExp pattern as follows:

• dots are allowed in the portion before @, but not as first or last character there
• the expression is anchored to start and end of string with ^ and $
• specific TLDs and country codes were added for the data I was using

The TLDs are whitelisted explicitly to exclude many typos where .com was entered as .co or .cm -- valid country codes for Colombia or Cameroon but not for my data.

The updated line:

SET @pattern = '^([a-zA-Z0-9_\-]+\.)*[a-zA-Z0-9_\-]+@([a-zA-Z0-9_\-]+\.)+(com|org|edu|net|ca|au|coop|de|ee|es|fm|fr|gr|ie|in|it|jp|me|nl|nu|ru|uk|us|za)$'

I hope this adds value to an already-excellent answer.

Unconformity

Answer 3

SELECT Email, CASE WHEN PATINDEX('%[ ]%', Email) > 0 THEN 'Invalid' -- Contiene espacios WHEN PATINDEX('%[^a-zA-Z0-9.%+-@]%', Email) > 0 THEN 'Invalid' -- Contiene caracteres no permitidos WHEN Email NOT LIKE '%@%.%' THEN 'Invalid' -- Patrón básico de email WHEN Email LIKE '%@%@%' THEN 'Invalid' -- Más de un @ WHEN Email LIKE '%..%' THEN 'Invalid' -- Dos puntos consecutivos WHEN LEN(Email) - LEN(REPLACE(Email, '.', '')) > 3 THEN 'Invalid' -- Más de 3 puntos ELSE 'Valid' END AS EmailStatus FROM YourTableName

Parmentier

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Earleneearley