CREATE TABLE #Email (
        TextCol text
);
INSERT INTO #Email VALUES( 'This is a test for getting the  email address out.' );
GO
SELECT RIGHT( t.Alias , CHARINDEX( SPACE( 1 ) , REVERSE( t.Alias ) ) - 1 ) +
        LEFT( t.Domain , CHARINDEX( SPACE( 1 ) , t.Domain ) -1 )  AS EmailAddress
FROM (
SELECT  SUBSTRING( TextCol , 1 , PATINDEX( '%@%', TextCol ) ) ,
        SUBSTRING( TextCol , PATINDEX( '%@%', TextCol ) + 1 , 255 )
FROM #Email
) AS t( Alias , Domain );
/*
EmailAddress
---------------------------

*/
GO
DROP TABLE #Email;
GO

This page was last updated on May 01, 2006 04:28 PM.