[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Full Text Search Problems MSSQL

Posted on 2006-05-18
6
Medium Priority
?
560 Views
Last Modified: 2012-06-27
Hi,

I'm having a problem where I have a full-text catalogue setup on a table "candidates" where the "CVText" field is the text that is indexed. Some our our clients have noticed that, when refering to a candidates name, no CV's are returned even thou the "CVText" field clearly contains it. The text is saved from our server via the Word Object and saved as Word HTML which sucks really as Word doesn't make a good job at all.

Would the HTML be causing a problem? Here is an example of "CVText":

------------------
<div class=Section1>
<p class=MsoTitle><i style='mso-bidi-font-style:normal'><span style='font-size:
24.0pt;font-family:"Arial Black";color:black;text-decoration:none;text-underline:
none'>ANDRE SANTANA<o:p></o:p></span></i></p>
................. etc
------------------

And part of the SQL statement is:
... INNER JOIN CONTAINSTABLE(candidates, *, '("ANDRE" AND "SANTANA")') AS K ...

The HTML, as bad as it is, does contain "ANDRE" AND "SANTANA" so why is it not being returned?

Thanks.
0
Comment
Question by:blandyuk
  • 2
  • 2
  • 2
6 Comments
 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16710564
Haven't worked with full text catalogues... but I do seem to recall that you'll need a second column to specify the DocumentType (file extension) of the column being indexed. The DocumentType is used to pick the appropriate filter... and its the filtering that is responsible for pulling out the actual bits of text.

This may only apply to columns of Image type.

See the "Filtering Supported File Types" page in SQL Books Online.

Rob
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1000 total points
ID: 16710579
>>Would the HTML be causing a problem? Here is an example of "CVText":<<
That could well be the case.  But first I would correct your syntax as follows (no paranthesis):

INNER JOIN CONTAINSTABLE(candidates, *, '"ANDRE" AND "SANTANA"') AS K ...

If that still fails than try this:
INNER JOIN CONTAINSTABLE(candidates, *, '">ANDRE" AND "SANTANA*"') AS K ...

Of course that could be a problem if the order of the names is reversed.
0
 
LVL 9

Author Comment

by:blandyuk
ID: 16716459
Ah! Thanks acperkins, It's returning the record now. The key attribute there is the * which I assume is a wildcard.

What other parameters can be specified? I noticed the > in the query as well. Just looked in our MCDBA book but there was no mention of these :(

Thanks.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 5

Expert Comment

by:rmacfadyen
ID: 16718108
One "advantage" to switching to a Image column and using a filter might be the index will consume considerably less data. Word produces fairly large HTML files and the HTML Filter will essentially pull out just the displayed text for the filter.

Regards,

Rob
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 16722958
>>The key attribute there is the * which I assume is a wildcard. <<
Not exactly, think of it as a Prefix Search and won't go wrong.

>>What other parameters can be specified?<<
They are all covered in BOL, namely:
simple
prefix
generation
proximity
weighted
boolean

>>I noticed the > in the query as well. Just looked in our MCDBA book but there was no mention of these <<
Not surprising as they have no meaning, other than they match the opening HTML tag :)
0
 
LVL 9

Author Comment

by:blandyuk
ID: 16723941
Ah! :) I see you were referring the the HTML with the > lol. I'll not be storing it as HTML from now on anyway. I never designed the system so I'm currently in the process of changing a lot of it.

Thanks.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

868 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question