July 10, 2010 at 3:09 pm
Comments posted to this topic are about the item TSQL Challenge 34-Search for two keywords within the maximum distance
July 13, 2010 at 4:29 am
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
July 13, 2010 at 10:19 am
You need to post it on the T-SQL Challenges site.
July 14, 2010 at 4:53 am
Thanks Steve. (Doh!)
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy