This is very similar in concept to [an earlier question](http://ask.sqlservercentral.com/questions/33457/extracting-data-in-sql-2005), in that you can get the answer the same way: a [tally table](http://www.sqlservercentral.com/articles/T-SQL/62867/). These things are pretty powerful and the performance is great. :-) Using the tally table, the answer from before--where you asked for getting a subsection of the data--is almost exactly the same as the one here: --Create a tally table. All Hail Jeff Moden! IF OBJECT_ID('dbo.Tally') IS NOT NULL DROP TABLE dbo.Tally SELECT TOP 11000 --equates to more than 30 years of dates IDENTITY(INT,1,1) AS N INTO dbo.Tally FROM Master.dbo.SysColumns sc1, Master.dbo.SysColumns sc2 ALTER TABLE dbo.Tally ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100 GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC --Now use the tally table to extract the relevant information. create table #CompanyInformation ( Id int, String varchar(1000) ); insert into #CompanyInformation values (1, 'ABC Company
firstname.lastname@example.org 123 Main St'); insert into #CompanyInformation values (2, 'DEF Company
email@example.com 123 Main St Floor 4'); SELECT SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) FROM dbo.Tally t cross join #CompanyInformation p WHERE N < LEN(' ' + p.String + ' ') AND SUBSTRING(' ' + p.String + ' ', N, 1) = ' ' AND SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) LIKE '%@%.%' drop table #CompanyInformation; The only difference from my answer to that earlier post is adding in one more where clause, where we're looking for an @ sign followed eventually by a dot (which you're unlikely to find in non-email field). That's one of the many awesome things about the tally table: it helps you solve an entire class of pattern-search problems, just like the ones you're running into now.
+1 to Kevin, but another option for this would be to use a CLR Scalar function - write the code in C#, get it to return the result. They have a much lower performance overhead than T-SQL Scalar Functions, so can yield very positive results, especially in this sort of area (string manipulation) where SQL Server has traditionally been weak.
SELECT CASE WHEN AtIndex=0 THEN '' --no email found ELSE RIGHT(head, PATINDEX('% %', REVERSE(head) + ' ') - 1) + LEFT(tail + ' ', PATINDEX('% %', tail + ' ')) END EmailAddress FROM (SELECT RIGHT(EmbeddedEmail, [len] - AtIndex) AS tail, LEFT(EmbeddedEmail, AtIndex) AS head, AtIndex FROM (SELECT PATINDEX('%[A-Z0-9]@[A-Z0-9]%', EmbeddedEmail+' ') AS AtIndex, LEN(EmbeddedEmail+'|')-1 AS [len], embeddedEmail FROM (SELECT 'The Imperial Oil Company
Phil.Factor@ImpOil.com 123 Main St') AS ListOfCompanies (EmbeddedEmail) )f )g /* EmailAddress -------------------------