0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people studying math at any level and professionals in related fields

; with texts_cte as
(
SELECT DISTINCT c2.Text AS Text
FROM Comments c1 JOIN Comments c2 ON (c1.PostId=c2.PostId)
  AND (c1.UserDisplayName='user' + Convert(nvarchar(20), ##UserId##)) AND (c1.CreationDate<c2.CreationDate)
WHERE (c2.Text LIKE '%@%')
)
, substrings_cte AS
(
SELECT substring(Text,charindex('@',Text), len(Text)) AS TextFrom
FROM texts_cte
),
names AS
(
SELECT substring(TextFrom,1,patindex('%[ :,.]%',TextFrom+' ')-1) AS Reply
FROM substrings_cte
)

SELECT Reply, Count(*) AS Count
FROM Names
GROUP BY Reply
ORDER BY Count(*) DESC

fork query

edited jan 8 20

created sep 30 19 Martin20528