Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqllover
Constraint Violating Yak Guru

India
338 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
Aged Yak Warrior

Romania
545 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
128 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
338 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  
 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.07 seconds. Powered By: Snitz Forums 2000