SQL with Manoj

The 2nd Challenge in SQL Server Geeks Facebook Page goes like this:

For the #SSGAS2015 attendees are leaving comments with their email ids. These comments gets saved into a table COMMENTS. You as an SQL Developer, need to extract all the email ids from the COMMENTS table.

–> Sample records in the table:

ID	Comment
1	Can I pay using PayUMoney. Please revert on ahmad.osama1984@gmail.com. 
        Also send details to manusqlgeek@gmail.com
2	I would like to get updates on SQLServerGeeks summit. Please send details 
        to myemailid@yahoo.com

–> Expected Answer:

ID	Emails
1	ahmad.osama1984@gmail.com, manusqlgeek@gmail.com
2	myemailid@yahoo.com

–> Initially this looked very tricky and tough question, but when you think by dividing the problem it looked very simple and here it goes:

1. First split the sentence into columns containing individual words by the whitespace separator.

2. Then Transpose all the columns as rows.

3. Then filter out the rows that contains email ID values.

4. Now for every ID column Transpost back the filtered rows into a single comma separated column value.
 

–> Here is the full solution:

-- Create the Table (DDL):
CREATE TABLE COMMENTS (
	ID INT, 
	Comment VARCHAR(1000)
)

-- Insert the 2 test rows:
INSERT INTO COMMENTS
SELECT 1, 'Can I pay using PayUMoney. Please revert on ahmad.osama1984@gmail.com. Also send details to manusqlgeek@gmail.com'
UNION ALL
SELECT 2, 'I would like to get updates on SQLServerGeeks summit. Please send details to myemailid@yahoo.com'

-- Check the rows:
select * from COMMENTS

-- Final solution as a single T-SQL Query:
;with CTE AS (
	SELECT A.ID, Split.a.value('.', 'VARCHAR(100)') AS Email
	FROM (SELECT ID, CAST ('<M>' + REPLACE(REPLACE(Comment,'. ', ' '), ' ', '</M><M>') + '</M>' AS XML) AS String
		FROM  COMMENTS) AS A
	CROSS APPLY String.nodes ('/M') AS Split(a)
)
, CTE2 AS (
	SELECT ID, Email
	FROM CTE
	WHERE Email like '%@%'
)
SELECT DISTINCT ID, (SELECT STUFF((SELECT ', ' + Email
 FROM CTE2
 WHERE ID  = t.ID
 FOR XML PATH('')),1,1,'')) AS Emails
FROM CTE2 t

-- Drop the table finally:
DROP TABLE COMMENTS
GO

Waiting for the coming challenges. Thanks!