InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 22:57:18 GMT20http://www.sqlservercentral.com/Forums/Topic1407340-149-1.aspxI just went through a similar exercise in this thread:[url]http://www.sqlservercentral.com/Forums/Topic1402134-391-1.aspx[/url]It's farily long but I think you'll find some useful examples in it. Towards the end of it is an example that snatches out multiple email addresses from a string.Tue, 15 Jan 2013 22:46:38 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1407340-149-1.aspxAs you said the data here is horrible to start with. Here is one way to deal with it. Use the DelimitedSplit8K function to first parse each string element apart based on spaces. Then take the list of all string segments and determine which ones are valid emails. The code for the DelimitedSpklit8K can be found by following the link in my signature about splitting strings.The email part is in fact quite timely. This topic is being discussed right now in another thread. [url=http://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspx]http://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspx[/url]I would recommend using the one that Lowell posted using System.Net.MailAddress as a way to validate the address. Of course this assumes you are able to use CLR. Using those two functions I was able to return 11 of the possible 12 emails in your sample code. The one that gets missed (Jerremyblack@gmail.comsnowwhite@ubisux.com) is just simply never going to be able to be determined with code.Here is the code that I came up with for this.[code]--this will split everything into segments first;with cte as( select * from email_Checker cross apply dbo.DelimitedSplit8K(Email_address, ' ')), ParseEmail as( select *, dbo.IsValidEmail(Item) as IsValidEmail from cte)select * from ParseEmailwhere IsValidEmail = 1[/code]Make sure you read and understand the code in both of these functions. The splitter function is super fast and easy to use, but understanding it might take a bit of reading and rereading. The CLR function is pretty straight forward.Tue, 15 Jan 2013 13:30:14 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1407340-149-1.aspxits not much better but here is another way that retrives any email address if its the last test in your string and has a space.[code="sql"]select reverse(substring(reverse(Email_Address),0,charindex(' ',reverse(Email_Address))))from DBO.Email_Checker[/code]Tue, 15 Jan 2013 12:57:45 GMTSGT_squeequalhttp://www.sqlservercentral.com/Forums/Topic1407340-149-1.aspxHi Guys,I have written a very hasty piece of code and have not been given much time to review this part but, because it is so hasty, I know in my water that this is not best practice, though it works and is picking up 85% of the email addresses (even with rubbish as well). It should be able to be written so that it picks up 9x% of clean data and I know that some of you have done similar work before.I am trying to pull out a single email address from data, Test data can be as shown below. As you can see, its pretty dirty. It doesn’t pick just the email address. It ignores any with more than one email address (while ideally the first would be best option) and I end up with just 1,7,8 and 9 with more than just email address.Ideally it should be all with possibly the exception of 4 which I don’t really know how to deal with and possibly 5. A quick table[code="plain"]Create Table DBO.Email_Checker(KeyField Int,Email_Address Varchar(75))[/code]Some Test Data[code="plain"]Insert Into DBO.Email_CheckerValues (1,'Freddy strange has an email address of freddy@gogo.com');Insert Into DBO.Email_CheckerValues (2,'Marge.flint@dhlink.co.uk / johnflint@gmail.com');Insert Into DBO.Email_CheckerValues (3,'Email: User1@retail.wholesale.co.uk & User1@Google');Insert Into DBO.Email_CheckerValues (4,'Jerremyblack@gmail.comsnowwhite@ubisux.com');Insert Into DBO.Email_CheckerValues (5,'Email USER2@giraffe,gaff');Insert Into DBO.Email_CheckerValues (6,'LinkLink@hydrager.eu – manklink@hydrager.eu');Insert Into DBO.Email_CheckerValues (7,'Johns sharp jsharp@fritty.co.jp');Insert Into DBO.Email_CheckerValues (8,'lankyman@gloopy.gov.gb');Insert Into DBO.Email_CheckerValues (9,'Mobile: 09120923123 Email hankerchief@dragon.boat.com');[/code]The Ugly Code - [code="plain"]select KeyField, Ltrim(Rtrim(Lower(Email_Address))) AS Emailfrom DBO.Email_CheckerWhere Lower(Email_Address) LIKE '%_@__%.__%'AND LEN(Email_Address) - LEN(REPLACE(Email_Address,'@','')) =1[/code]Drop Table[code="plain"]--Drop Table DBO.Email_Checker[/code]Any ideas?Tue, 15 Jan 2013 09:41:15 GMTGattaca