• Status: Solved
• Priority: Medium
• Security: Public
• Views: 229

# Create a function

How can I turn this select statement into a function that will return true if the email address appears valid and false if it doesn't. This select statement works, but only returns bad email addresses.

``````SELECT *  FROM ARCUSFIL_SQL WHERE NOT (
``````
0
red_75116
• 3
1 Solution

Commented:
Have you pasted the right bit? In the right forum?
0

Commented:
Here is the syntax for creating a function
http://msdn.microsoft.com/en-us/library/ms186755.aspx

Look at the "return" keyword - this will allow you to return true/false based upon your select statement within the function
0

Commented:
someones done one here

It's basic but might do the trick if needed to be bulletproof
Bound to be heaps
0

Commented:
like this ?

C
``````REATE FUNCTION dbo.fnValidateEmail(@EMAIL varchar(100))
returns bit
as
Begin
return (
Select case when email like '_%@__%.__%'            -- basic valid pattern x@xx.xx
then case when charindex(' ',email) = 0 -- no spaces
and left(liame,1)<>'.'       -- not end with .
and charindex('.@',email)=0  -- name cant end in .
and charindex('..',email)=0  -- cant have ..
and charindex('.',liame)>=3  -- suffix must be at least 2 char
-- handled by pattern  period after @
--         and CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) > 1
and LEN([Email]) - LEN(REPLACE([Email],'@','')) = 1 -- only 1 @
then 1
else 0
end
else 0
end
from (select email,reverse(email) as liame
from (select ltrim(rtrim(coalesce(@email,''))) as email) as x
) as y
)
End
``````
0

Author Commented:
Works perfectly!  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.