Solved

# Create a function

Posted on 2011-09-25
154 Views
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
Question by:red_75116
• 3

LVL 29

Expert Comment

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

LVL 29

Expert Comment

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

LVL 29

Expert Comment

someones done one here

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

LVL 50

Accepted Solution

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 Closing Comment

Works perfectly!  Thanks!
0

## Featured Post

### Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backupâ€¦
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.