March 19, 2010 at 6:39 am
I have created a full text index on a table that contains contact and company names. Wher users need to search this index for 'Bill Smith' its fine bu when its is entered into the table as 'B Smith' the 'B' is regarded as a noise word and hence ignored. I've tried removing single letters from the stopword table but this doesnt seem to have helped. It works fine on names without initials but I cant see any obvious way of dealing with them. Any Ideas ?
March 19, 2010 at 7:20 am
Several ideas. Can you post a (simplified if necessary) example table, a few rows of data, and the full-text query you are currently using? More detail on the exact requirements for a match would help too. I am happy to create my own full-text catalog, indexes, and such. Thank you.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 8:22 am
I have attached a table and some data plus a very simple query.
March 19, 2010 at 8:26 am
What is the base data type for the alias type [dbo].[COMMENT]?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 8:33 am
text field but its not really used, for these purposes we can just drop it
March 19, 2010 at 8:52 am
Martin Stephenson (3/19/2010)
text field but its not really used, for these purposes we can just drop it
Ok. I guessed at VARCHAR(100) in the end. I also added an ID column to match the CSV file.
The sample query is
select NAME_UNO, NAME
from HBM_NAME HN
where contains (NAME, '"A Hill*"')
This gives me:
NAME_UNO NAME
116795 (Dec'd) A Hill
117215 (Dec'd) A Hill
120958 (Dec'd) A Hill
Do you want to say some more about what output you expect, and what the 'rules' should be to achieve that?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 19, 2010 at 9:18 am
Not a great example but the data set is a bit limited.
If I wanted to find 'B & S' there would be no results returned because the full text index regards the entry from the database as noise, 'B & S Ltd' would be indexed as Ltd.
With the example query I gave you, if the data set also contained 'B Hill' and 'C Hill' they also would be returned in the search results.
March 19, 2010 at 9:31 am
Martin Stephenson (3/19/2010)
Not a great example but the data set is a bit limited.If I wanted to find 'B & S' there would be no results returned because the full text index regards the entry from the database as noise, 'B & S Ltd' would be indexed as Ltd.
Not if you configure the stoplist and stopwords correctly. I used STOPLIST = OFF for simplicity here.
With the example query I gave you, if the data set also contained 'B Hill' and 'C Hill' they also would be returned in the search results.
That depends entirely on how you write the full-text query. They would not be returned in my configuration for example, with the phrase-based full-text query you supplied.
I would suggest you start by writing a specification of the rules you want to apply when searching, load some real data, and experiment with the different full-text syntaxes and stopwords. It is most important that you agree how things should work with the eventual users of the system too!
It is difficult to assist you much further at this stage. Post back when things are clearer, and you have some representative data.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply