CREATE TABLE #Numbers ( n int NOT NULL ); INSERT INTO #Nu...

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 #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;

Enter Parameters

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