Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered: Email Address Validation

    Hi,

    I am using the below function to validate the email address
    Code:
    ALTER FUNCTION [dbo].[udf_ValidateEmail] (@email varChar(255))
    
    RETURNS bit
    AS
    begin
    return
    (
    select 
    	Case 
    		When 	@Email is null then 0	                	--NULL Email is invalid
    		When	charindex(' ', @email) 	<> 0 or		--Check for invalid character
    				charindex('/', @email) 	<> 0 or --Check for invalid character
    				charindex(':', @email) 	<> 0 or --Check for invalid character
    				charindex(';', @email) 	<> 0 then 0 --Check for invalid character
    		When len(@Email)-1 <= charindex('.', @Email) then 0--check for '%._' at end of string
    		When 	@Email like '%@%@%'or 
    				@Email Not Like '%@%.%'  then 0--Check for duplicate @ or invalid format
    		Else 1
    	END
    )
    end
    but it is not validating whether i have .com/.org/.net/.co.uk/.co.in/.in

    which means it should have to check i must have domain name after dot(.)

    please help me on validating the email address

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    CharIndex() finds the first occurrence of a string.

    Therefore CharIndex('.', @Email) will be returning the first period. Incidentally, email addresses can have periods in before the @ symbol too

    To search for the last period you want to use Reverse() first.
    George
    Home | Blog

  3. #3
    Join Date
    Apr 2011
    Posts
    27
    Hi Gvee,

    thanks for your time on this. Finally i ended up with writing CLR functions and i am able to achieve

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Google for a tool or regular expression

    Finally I ended up with writing CLR functions
    look at packages for addresses, email, etc validation.

  5. #5
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    i am using MS.net and sqlserver2008R2. So i am able to write the CLR procedure and using regex am able to achieve the email address validation.

    Thank you.

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    You missed my point! An email is complicated and needs a "regular expression from Hell" to be sure it is right. This is hard to write, so you are better off with a package instead of doing ti yourself

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Celko View Post
    You missed my point! An email is complicated and needs a "regular expression from Hell" to be sure it is right. This is hard to write, so you are better off with a package instead of doing ti yourself
    Where's the fun in that?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    I am passing the below regex to match my requirement and it works as expected.

    Code:
    select dbo.RegExMatch('memymail.','^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,6}$')
    I am not sure what do you meant by packages.

  9. #9
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    I am not sure what do you meant by packages.
    Things like Melissa Data that put street addresses into CASS format, check phone numbers, validate emails, etc.
    Email Solutions | IP Solutions | Melissa Data

    Since UNICODE just got added to emails, life is too short to have fun

  10. #10
    Join Date
    Apr 2011
    Posts
    27
    Thank You Celko.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •