Creating a function to validate Email ID and return 1 if valid and 0 if invalid

Question

This is my code:

CREATE FUNCTION [dbo].[fnAppEmailCheck]
    (@email VARCHAR(255))   
    --Returns true if the string is a valid email address.  
RETURNS bit  
AS
BEGIN  
     DECLARE @valid bit  

     IF @email IS NOT NULL   
          SET @email = LOWER(@email)  

     SET @valid = 0  

     IF @email LIKE '[a-z,0-9,_,+,-]%@[a-z,0-9,-]%.[a-z][a-z]%'
        AND LEN(@email) = LEN(dbo.fnAppStripNonEmail(@email))  
        AND @email NOT like '%@%@%' 
        AND CHARINDEX('.@',@email) = 0
        AND PATINDEX('%[a-zA-Z]%', @email) <> 0
        AND CHARINDEX('..',@email) = 0  
        AND CHARINDEX(',',@email) = 0              
        AND RIGHT(@email,1) between 'a' AND 'z'  
            SET @valid = 1  

     RETURN @valid  
END

My fnAppStripNonEmail function :

CREATE FUNCTION [dbo].[fnAppStripNonEmail]
    (@Temp VarChar(1000))
RETURNS VarChar(1000)
AS
BEGIN
    DECLARE @KeepValues AS varchar(50)
    SET @KeepValues = '%[^a-z,^0-9,@,.,-]%'

    WHILE PATINDEX(@KeepValues, @Temp) > 0
        SET @Temp = STUFF(@Temp, PATINDEX(@KeepValues, @Temp), 1, '')
    
    RETURN @Temp
END

In the screenshot, every format works fine as per my requirements but it flags 22@gmail.com as 1. I want my email ID to have at least one alphabet.

Can you plase provide a text sample of your input data. It's much easier to use it to try out something by copying the text than an image. — Pubudu Sitinamaluwa, Jul 15 '21 at 17:25
Why do you assume that `22@gmail.com` is invalid? You can't validate an email address with simple patterns or even with regular expressions.[This SO question](https://stackoverflow.com/questions/201323/how-can-i-validate-an-email-address-using-a-regular-expression) has some very detailed and complex answers and yet even the best will miss cases. The "simple" answers fail even the simplest tests, like `somename@localhost`, a perfectly valid address — Panagiotis Kanavos, Jul 15 '21 at 17:31
The results show the function rejects a lot of valid addresses. If you want a half-way decent validation you should use a SQLCLR function able to perform the necessary validation. The article [How to verify that strings are in valid email format)](https://learn.microsoft.com/en-us/dotnet/standard/base-types/how-to-verify-that-strings-are-in-valid-email-format) normalizes the input, handles Uniicode (which *is* valid), and then uses a *demo* regular expression to validate the email. The article goes to great lengths to warn that simple pattern matching isn't enough — Panagiotis Kanavos, Jul 15 '21 at 17:38
You can also check [the regex used](https://github.com/microsoft/referencesource/blob/master/System.ComponentModel.DataAnnotations/DataAnnotations/EmailAddressAttribute.cs) by the `EmailAddressAttibute`. It's *very* long — Panagiotis Kanavos, Jul 15 '21 at 17:41
There's a really good discussion and a 99% solution here: https://regular-expressions.mobi/email.html — Chris Maurer, Jul 15 '21 at 19:01
After your `IF @email IS NOT NULL` check, you had **two** `SET ....` statement indented - which leads me to believe you thought these two statements would both be executed only when the `IF` check succeeds - **THAT IS NOT THE CASE!** If you want to execute *more than ONE statement* in a `IF` block, you **MUST** use `BEGIN ....... END` - otherwise, **only** the first statement after the `IF` check is "protected" by that check! Only one ... — marc_s, Jul 15 '21 at 19:37
As per the question guide, please do not post images of code, data, error messages, etc. - copy or type the text into the question. Please reserve the use of images for diagrams or demonstrating rendering bugs, things that are impossible to describe accurately via text. — Dale K, Jul 15 '21 at 20:10

score 1 · Answer 1 · answered Jul 15 '21 at 17:56

CREATE FUNCTION dbo.CheckValidEmail(@EMAIL varchar(100))RETURNS bit as
BEGIN     
  DECLARE @bitEmailVal as Bit
  DECLARE @EmailText varchar(200)

  SET @EmailText=ltrim(rtrim(isnull(@EMAIL,'')))

  SET @bitEmailVal = case when @EmailText = '' then 0
                          when @EmailText like '% %' then 0
                          when @EmailText like ('%["(),:;<>\]%') then 0
                          when substring(@EmailText,charindex('@',@EmailText),len(@EmailText)) like ('%[!#$%&*+/=?^`_{|]%') then 0
                          when (left(@EmailText,1) like ('[-_.+]') or right(@EmailText,1) like ('[-_.+]')) then 0                                                                                    
                          when (@EmailText like '%[%' or @EmailText like '%]%') then 0
                          when @EmailText LIKE '%@%@%' then 0
                          when @EmailText NOT LIKE '_%@_%._%' then 0
                          else 1 
                      end
  RETURN @bitEmailVal
END 
GO

Creating a function to validate Email ID and return 1 if valid and 0 if invalid

1 Answers1