Thursday, 13 April 2017

EmailId Validation IN Sql Server

Create a User defined function to Validate EmailID in Sql Server

Create FUNCTION UDFValidateEmail (@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)-<= 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

Now call the Udf to validate  Email

SELECT dbo.UDFValidateEmail('suraj@dotnetguru.in')

Valid Email returns 1 while Invalid Email returns 0 as output.

0 comments:

Post a Comment

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)