Email Address Validation - SQL Server Forums

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

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Email Address Validation

Author

Topic

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-20 : 18:18:08

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
[/code]
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

545 Posts

Posted - 2014-03-21 : 02:08:42

maybe this

 CASE WHEN @email LIKE '%_@_%_.__%'

sabinWeb MCP

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-03-21 : 05:47:47

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.......

sqllover
Constraint Violating Yak Guru

338 Posts

Posted - 2014-03-21 : 07:25:58

Thank you Guys. Finally the i ended up with writing CLR functions and i am able to achieve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources