How to extract only email id in SQL SERVER

Question

How do I extract just the email id from the example below (i.e. abc@abc.com , mno@mno.com , etc.)? Each line can contain multiple email IDs ...

"ABC" <abc@abc.com>; "XYZ" <xyz@xyz.com>; pqr@pqr.com; "MNO" <mno@mno.com>

+3

user4156123 10 jul. 15 at 5:37 am

3 answers

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(email,'>',1),'<',-1) AS EmailID
FROM table_name;

+1

Sadikhasan 10 jul. 15 at 5:46 am

In SQL Server run this query

SELECT SUBSTRING ( Emailcolumn ,CHARINDEX('<',Emailcolumn )+1
 , CHARINDEX('>',Emailcolumn )-CHARINDEX('<',Emailcolumn )-1)

0

Mukesh Kalgude 10 jul. 15 at 5:47 am

Stanislovas Kalašnikovas · Accepted Answer · 2015-07-10T05:44:51+0000

In MySQL, you can use SUBSTRING_INDEX

the following:

SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(Id, '>', 1), '<', -1) Email
FROM Tbl;

SQL Server will:

SELECT SUBSTRING(Id, CHARINDEX('<', Id) + 1 , CHARINDEX('>', Id) - CHARINDEX('<', Id) - 1)
FROM Tbl;