SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Email Address Validation
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 03/20/2014 :  18:18:08  Show Profile  Reply with Quote
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

stepson
Constraint Violating Yak Guru

Romania
400 Posts

Posted - 03/21/2014 :  02:08:42  Show Profile  Reply with Quote
maybe this
CASE WHEN @email LIKE '%_@_%_.__%'



sabinWeb MCP
Go to Top of Page

MuralikrishnaVeera
Posting Yak Master

India
106 Posts

Posted - 03/21/2014 :  05:47:47  Show Profile  Reply with Quote
In the following way you can able to write number of conditions Until your requirement has reached........

CREATE FUNCTION [dbo].[udf_ValidateEmail] (@email varChar(255))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @temp VARCHAR(50)
IF (@email is Null)
SET @temp = ' EmailAdress not valid'
ELSE IF ( @email like '%[!#$%^&*()_+ <>?;:"{}]%')
SET @temp ='Special Characters are not Allowed...'
ELSE IF ( (PATINDEX('%@%.%.%',@email) <>0))
SET @temp = 'You have Entered Incorrect Mail..'
ELSE
SET @temp = 'You are Sucessfully LogedIn....'
RETURN @temp
END

Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

sqllover
Constraint Violating Yak Guru

India
331 Posts

Posted - 03/21/2014 :  07:25:58  Show Profile  Reply with Quote
Thank you Guys. Finally the i ended up with writing CLR functions and i am able to achieve
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000