T-SQL Query solution to SSGAS2015 2nd Question/Challenge – SQL Server Geeks
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 email@example.com. Also send details to firstname.lastname@example.org 2 I would like to get updates on SQLServerGeeks summit. Please send details to email@example.com
–> Expected Answer:
ID Emails 1 firstname.lastname@example.org, email@example.com 2 firstname.lastname@example.org
–> 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 email@example.com. Also send details to firstname.lastname@example.org' UNION ALL SELECT 2, 'I would like to get updates on SQLServerGeeks summit. Please send details to email@example.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!