Geek asks and answers

SQL Email Validation Feature Using Regex

I am trying to create a function in SQL Server 2005 to check if an email matches the correct regular expression format.

Here is what I still have:

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 something wrong with my regex? Or do I need to do something more to compare varchar with regex?

-Edit-

Right now, any line that I insert returns 0, even if the email format itself is correct.

+5
source share
2 answers

Short answer: no, this is not possible. LIKE syntax is not the same (and less powerful than) regular expressions. See Also SQL Server Regular Expressions in T-SQL

.Net . VBScript.RegExp T-SQL, sp_OACreate .

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

Regex - JavaScript, , , .

+5

@flup RegExp :

  • @,
  • ^ $
  • , TLD .

TLDs , .com .co .cm - , .

:

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)$'

, .

+2

More articles:


All Articles