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

Question

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

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

Lawrence Moore Ten Centuries Points: 1203 More actions · Answer 1

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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

SSC Guru

Points: 736478

July 13, 2010 at 10:19 am

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

Lawrence Moore Ten Centuries Points: 1203 More actions · Answer 3

Ten Centuries

Points: 1203

July 14, 2010 at 4:53 am

Thanks Steve. (Doh!) 😉