Solved

Create a function

Posted on 2011-09-25
5
198 Views
Last Modified: 2012-05-12
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 (
 	CHARINDEX(' ',LTRIM(RTRIM([Email_addr]))) = 0  AND
  	LEFT(LTRIM([Email_addr]),1) <> '@'  AND
  	RIGHT(RTRIM([Email_addr]),1) <> '.'  AND
  	CHARINDEX('.',[Email_addr],CHARINDEX('@',[Email_addr])) - CHARINDEX('@',[Email_addr]) > 1  AND
  	LEN(LTRIM(RTRIM([Email_addr]))) - LEN(REPLACE(LTRIM(RTRIM([Email_addr])),'@','')) = 1  AND 
 	CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email_addr])))) >= 3  AND
  	(CHARINDEX('.@',[Email_addr]) = 0 AND CHARINDEX('..',[Email_addr]) = 0)  )

Open in new window

0
Comment
Question by:red_75116
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 29

Expert Comment

by:QPR
ID: 36596785
Have you pasted the right bit? In the right forum?
0
 
LVL 29

Expert Comment

by:QPR
ID: 36596848
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

by:QPR
ID: 36596851
someones done one here
http://www.nimbleuser.com/blogs/Developers/developers/SQL_Function_to_Validate_email_address_syntax.aspx

It's basic but might do the trick if needed to be bulletproof
Failing that, google: t-sql validate email address
Bound to be heaps
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 36598089
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(email,1)<>'@'       -- not start with @
                       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

Open in new window

0
 

Author Closing Comment

by:red_75116
ID: 36717922
Works perfectly!  Thanks!
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Stored Proc - Performance Enhancement 15 86
Negative isnull? 3 31
SQL Server for XML PATH giving wrong results. 6 59
What is needed to become a DBA? 7 51
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question