Solution to TSQL Challenge 34 - Search for two keywords within the maximum distance of one word between them By malpashaa

WITH Text1PhrasesCTE AS
(
   SELECT P.TextID, ' ' + S.Text1 + ' ' AS text1,
          SUBSTRING(' ' + P.Data + ' ', CHARINDEX(' ' + S.Text1 + ' ', ' ' + P.Data + ' ') + LEN(' ' + S.Text1 + ' '), 2147483647) AS data
     FROM (SELECT DISTINCT Text1
             FROM TC34_Searches) AS S
          INNER JOIN
          TC34_Phrases AS P
          ON CHARINDEX(' ' + S.Text1 + ' ', ' ' + P.Data + ' ') > 0

    UNION ALL

   SELECT TextID, text1, SUBSTRING(data, CHARINDEX(text1, data) + LEN(text1), 2147483647)
     FROM Text1PhrasesCTE
    WHERE CHARINDEX(text1, data) > 0
)
, Text2PhrasesCTE AS
(
   SELECT P.TextID, ' ' + S.Text2 + ' ' AS text2,
          SUBSTRING(' ' + P.Data + ' ', CHARINDEX(' ' + S.Text2 + ' ', ' ' + P.Data + ' ') + LEN(' ' + S.Text2 + ' '), 2147483647) AS data
     FROM (SELECT DISTINCT Text2
             FROM TC34_Searches) AS S
          INNER JOIN
          TC34_Phrases AS P
          ON CHARINDEX(' ' + S.Text2 + ' ', ' ' + P.Data + ' ') > 0

    UNION ALL

   SELECT TextID, text2, SUBSTRING(data, CHARINDEX(text2, data) + LEN(text2), 2147483647)
     FROM Text2PhrasesCTE
    WHERE CHARINDEX(text2, data) > 0
)
SELECT DISTINCT S.SearchID, T1.TextID
  FROM TC34_Searches AS S
       INNER JOIN
       Text1PhrasesCTE AS T1
       ON T1.text1 = ' ' + S.Text1 + ' '
 WHERE EXISTS(SELECT *
                FROM Text2PhrasesCTE AS T2
               WHERE T2.TextID = T1.TextID
                 AND T2.text2 = ' ' + S.Text2 + ' '
                 AND CASE WHEN LEN(T1.data) - LEN(T2.data) - LEN(T2.text2) >= 0
                               THEN CHARINDEX(' ', RTRIM(LTRIM(LEFT(T1.data, LEN(T1.data) - LEN(T2.data) - LEN(T2.text2)))))
                          ELSE CASE WHEN LEN(T2.data) - LEN(T1.data) - LEN(T1.text1) >= 0
                                         THEN CHARINDEX(' ', RTRIM(LTRIM(LEFT(T2.data, LEN(T2.data) - LEN(T1.data) - LEN(T1.text1)))))
                                    ELSE 1
                               END
                     END = 0)
 ORDER BY S.SearchID, T1.TextID
OPTION(MAXRECURSION 0);

Performance stats of the above solution

Sr# StartTime                 Reads      Writes         CPU Duration 
--- ------------------- ----------- ----------- ----------- -------- 
  1 Dec 10 2010 11:37AM      546478        9262        2652    2.968
  2 Dec 10 2010 12:48PM      548348        9262        2714    3.203
  3 Dec 10 2010  2:00PM      548026        9262        2746    3.100
  4 Dec 10 2010  3:11PM      547537        9263        2668    3.042
  5 Dec 10 2010  4:22PM      547909        9261        2762    3.299