TSQL Challenge 34-Search for two keywords within the maximum distance

  • Comments posted to this topic are about the item TSQL Challenge 34-Search for two keywords within the maximum distance

  • This works. (can probably be simplified)

    ;WITH WordsFromPhrases(Textid, WordPos, Word, NewPhrase)

    AS

    (

    SELECT TextId, 1 AS 'WordPos', SUBSTRING(Data, 1, CHARINDEX(' ', Data, 1)) AS 'Word',

    REPLACE(Data, SUBSTRING(Data, 1, CHARINDEX(' ', Data, 1)), '') + ' ' AS 'NewPhrase'

    FROM tc34_phrases

    UNION ALL

    SELECT TextId, WordPos+1, SUBSTRING(NewPhrase, 1, CHARINDEX(' ', NewPhrase, 1)) AS 'Word',

    REPLACE(NewPhrase, SUBSTRING(NewPhrase, 1, CHARINDEX(' ', NewPhrase, 1)), '') AS 'NewPhrase'

    FROM WordsFromPhrases WHERE NewPhrase<>''

    )

    SELECT SearchId, TextId FROM

    (

    SELECT searchid, textid, MAX(WordPos1) AS WordPos1, MAX(Word1) AS Word1, MAX(WordPos2) AS WordPos2, MAX(Word2) AS Word2 FROM

    (

    SELECT SearchId, TextId, WordPos AS 'WordPos1', Word AS 'Word1', NULL AS 'WordPos2', NULL AS 'Word2' FROM WordsFromPhrases w2

    INNER JOIN tc34_searches s2 ON s2.text2=w2.Word

    UNION ALL

    SELECT SearchId, TextId, NULL, NULL, WordPos ,Word FROM WordsFromPhrases w1

    INNER JOIN tc34_searches s1 ON s1.text1=w1.Word

    ) q1

    GROUP BY searchid, textid

    )q2

    WHERE q2.WordPos1-q2.WordPos2 BETWEEN 1 AND 2

    OR q2.WordPos2-q2.WordPos1 BETWEEN 1 AND 2

    ORDER BY searchid, textid

  • You need to post it on the T-SQL Challenges site.

  • Thanks Steve. (Doh!) 😉

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply