get output as Email Address - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

get output as Email Address

Author

Topic

Najju565
Starting Member

6 Posts

Posted - 2014-01-28 : 05:41:05

Display Name Column in DISPLAY_DETAILS Table contains data as: Joe Barnard(123456)<Joe.Barnard@SQLTeam.com>;Paul Johnson(114454)<Paul.Johnson@SQlTeam.com>;

Need to pull Email Address of Individual Employees.
need result like this:

Joe.Barnard@SQLTeam.com;Paul.Johnson@SQlTeam.com

Please help on the same.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-28 : 06:00:55

[code]
;WIth CTE
AS
(
SELECT t.PK,REPLACE(STUFF(f.Val,1,CHARINDEX('<',f.Val),''),'>','') AS EmailAddr
FROM DISPLAY_DETAILS t
CROSS APPLY dbo.ParseValues(t.DisplayName,';')f
)

SELECT PK,
STUFF((SELECT ';' + EmailAddr FROM CTE WHERE PK = t.PK FOR XML PATH('')),1,1,'')
FROM (SELECT DISTINCT PK FROM CTE)t
[/code]

PK is primary key of your table
ParseValues can be found here
http://visakhm.blogspot.com/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources