Advertisements
Home > SQL Tips > T-SQL Query solution to ‎SSGAS2015‬ 2nd Question/Challenge – SQL Server Geeks

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 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!


Advertisements
  1. No comments yet.
  1. June 13, 2015 at 8:19 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: