SQL Email Verification Function using Regex

regex, sql, sql-server-2005

Solution

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.

Problem

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.

Original source

Related problems