Q&A for professional and enthusiast programmers
CREATE TABLE #Numbers ( n int NOT NULL ); INSERT INTO #Numbers SELECT TOP 250 ROW_NUMBER() OVER (Order By Id) FROM Posts; CREATE TABLE #Titles (TitleOrig varchar(250) NOT NULL, Title varchar(250) NOT NULL, Closed bit NOT NULL ); INSERT INTO #Titles (TitleOrig, Title, Closed) SELECT TOP 50000 Title, ' ' + LOWER(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(CAST(P.Title as varchar(250)), ',', ' '), '?', ''), '*',''), '/', ''), ':', ''), '"', ''), '(', ''), ')', ''), '''', ''),'`',' '), '<', ''),' ',' ')) + ' ', CASE WHEN PH.id IS NOT NULL THEN 1 ELSE 0 END FROM Posts P LEFT JOIN PostHistory PH ON PH.PostId = P.Id AND PH.Comment = '3' AND PH.PostHistoryTypeId = 10 WHERE P.Title IS NOT NULL AND P.PostTypeId = 1 ORDER BY P.Id DESC; -- reverse chronological creation WITH TitleWords (TOrig, Title, Word, Closed) AS ( SELECT TitleOrig, Title, SubString(Title, n + 1 , CHARINDEX(' ', Title, n + 1) - n ), Closed FROM #Titles T INNER JOIN #Numbers N ON SUBSTRING(T.Title, N.n, 1) = ' ' WHERE N.n < LEN(T.Title) - 1 ) SELECT * FROM TitleWords TW WHERE TW.Word LIKE '%develop%' DROP TABLE #Titles; DROP TABLE #Numbers;