Extract email from string

This is very similar in concept to an earlier question, in that you can get the answer the same way: a tally table. 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 jr68@gmail.com 123 Main St');
insert into #CompanyInformation values (2, 'DEF Company jr68222@gmail.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
-------------------------
Phil.Factor@ImpOil.com
*/

Use Send DB Mail Procedure and Return Email id ...............