CREATE TABLE #Titles (PostId bigint, Title nvarchar(250) );

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for professional and enthusiast programmers

CREATE TABLE #Titles (PostId bigint, Title nvarchar(250) );

INSERT INTO #Titles (PostId, Title)
SELECT TOP 10 P.Id, Replace(Replace(Replace(Replace(Replace(P.Title, ',', ' '), '?', ''), '  ',' '), '/', ''), ':', '')
FROM Posts P
   INNER JOIN PostHistory PH ON PH.PostId = P.Id
WHERE PH.Comment = '3';

CREATE TABLE #StuffToIgnore ( Word varchar(250) );
INSERT INTO #StuffToIgnore
VALUES ('it')
       , ('for')
       , ('to')
       , ('is')
       , ('a')
       , ('the')
       , ('and')
       , ('of')
       , ('are')
       , ('as')
       --, ('what')
       --, ('how')
       ;       

WITH TitleWords (PostId, Word, Rem) AS (
   SELECT PostId, SUBSTRING(Title, 1, CHARINDEX(' ', Title + ' ')), SUBSTRING(Title, CHARINDEX(' ', Title + ' ') + 1, LEN(Title))
   FROM #Titles T
   
   UNION ALL
   
   SELECT PostId, SUBSTRING(Rem, 1, CHARINDEX(' ', Rem + ' ')), SUBSTRING(Rem, CHARINDEX(' ', Rem + ' ') + 1, LEN(Rem))
   FROM TitleWords
   WHERE LEN(Rem) > 0
)
SELECT TOP 10 TW.Word, COUNT(PostId) AS NumOccurred
FROM TitleWords TW
   LEFT JOIN #StuffToIgnore STI ON STI.Word = TW.Word
WHERE STI.Word IS NULL
GROUP BY TW.Word
ORDER BY COUNT(*) DESC;

DROP TABLE #Titles;
DROP TABLE #StuffToIgnore;

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results