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

• Press Release

  SSCarpal Tunnel

  Points: 4393

  More actions

  July 10, 2010 at 3:09 pm

  #221080

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

• Lawrence Moore

  Ten Centuries

  Points: 1203

  More actions

  July 13, 2010 at 4:29 am

  #1191882

  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: 738277

  More actions

  July 13, 2010 at 10:19 am

  #1192207

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

• Lawrence Moore

  Ten Centuries

  Points: 1203

  More actions

  July 14, 2010 at 4:53 am

  #1192665

  Thanks Steve. (Doh!)