Binaryworld - Stripping Email Address from a text or character value. ... [ SQL Server -> T-SQL ]

This simple example shows a powerful way to manipulate values in text columns. This one can be used to determine the email address from a suitably formatted text value.

Click here to copy the following block

CREATE TABLE #Email (
    TextCol text
);
INSERT INTO #Email VALUES( 'This is a test for getting the notify@mail.umachandar.com 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
---------------------------
notify@mail.umachandar.com
*/
GO
DROP TABLE #Email;
GO