Full Text Search causing headaches

• laric

  SSC Rookie

  Points: 32

  More actions

  December 16, 2003 at 1:56 am

  #107030

  I have a table with a full text indexed column called txt.

  I am trying to develop a simple web search for this column but I am getting problems.

  For instance I have 4 records that has the following data in the txt column

  'Laminatg 10x4'

  'Laminat Gulv Rød'

  'GULV blå'

  'LAMINATGULV'

  if the user enters laminat gulv in the input box he wants all entries where laminat AND gulv is. If the user enters laminat he should get all occurances of laminat (including LAMINATGULV and laminatg)

  The problem is that I can't figure out a way to solve the full-text search.

  I can't get anything to return the correct values.

  I am running SQL server 2000 sp 3a on a w2k server sp4.

  I have heard that full text is based on the OS. Would anything be solved if I upgraded to win 2003 server?

  Please please help.

• mccork

  SSC-Insane

  Points: 22288

  More actions

  December 16, 2003 at 4:20 am

  #486346

  I wouldn't expect upgrading to win 2003 to present different results - or there'd be people screaming at MS by now.

  I may be in your full text queries. What are the structure of them?

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• DavidT

  SSCommitted

  Points: 1727

  More actions

  December 16, 2003 at 5:03 am

  #486359

  Basic full text searching only returns whole word matches.

  Therefore entering Laminat will not return Laminatg, etc...

  However full text does support a starts with approach using the * symbol.

  The query you need is:

  select * from Table where contains(Column,'"Laminat*"')

  rather than:

  select * from table where contains(Column,'Laminat')

  Note the extra double quotes and the *.

  David

• laric

  SSC Rookie

  Points: 32

  More actions

  December 16, 2003 at 5:08 am

  #486360

  I was more thinking along the lines of a query like this

  select * from table where contains(txt,'"*laminat*" and "*gulv*"')

  but this sure doesn't work.

  I can't know if the word begins or ends with the statement.

• DavidT

  SSCommitted

  Points: 1727

  More actions

  December 16, 2003 at 5:14 am

  #486362

  Hmm, full text doesn't support an asterix at the start of the word (ie: an end with or contains search).

  Do you have to use full text? (how big is the table?)

  Could you revert back to

  select * from table

  where txt like '%laminat%'

  or txt like '%gulv%'

  ....

  Edited by - DavidT on 12/16/2003 05:29:11 AM