As 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. http://www.sqlservercentral.com/Forums/Topic1406771-386-1.aspx

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.

--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 ParseEmail

where IsValidEmail = 1

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.