• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 749
  • Last Modified:

FreeText Search Doesn't Work

I am building a search page to search through a database table on an ms sql 2000 server. I have Full-Text Indexing Enable, and it scheduled to populate all of which work fine. The problem I am having is that when I run the full text search, It is not returning accurate information.

Here is my sql statement, I have ran it through Query Analyzer.

Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'Kenny');

Example of Data in Database
N     Question       Answer
1      Test             <P>Kenny</P>
2      Test             Kenny
3      Test             Kenny2

It is my understanding that from my SQL Statment above, It should return All rows. However, it is only returning the Second Row. However, if I search for Test, It does return all rows. It looks like it matches exact wording. But I was under the impression that a FREETEXT search was more of a Fuzzy search.

thanks in advance for any help
0
ktrimmer
Asked:
ktrimmer
  • 13
  • 9
  • 6
  • +3
3 Solutions
 
mattisflonesCommented:
where freetext like 'kenny'
0
 
mattisflonesCommented:
where coloumn like 'syntax' that is..
0
 
ktrimmerAuthor Commented:
Okay, I tried
Select * From dbo.PV_ITS_Global_FAQ Where  LIKE Freetext(*,'Kenny');
and got a syntax error around LIKE

I am not sure what you mean by the second post.
Thanks,
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LowfatspreadCommented:
which language have you enabled as the default for fulltext...
which collation sequences do the columns have
0
 
mattisflonesCommented:
Youll need Where COLOUMNNAME like 'syntax' (try just 'kenny' first, dont know about *,' ')
0
 
ktrimmerAuthor Commented:
Not sure about the language, I follwed the wizard. I will look into that.

The page is being designed using ASP.NET with VB.NET.

I have selected to index Both Question and Answer.

Hope that helps
0
 
mattisflonesCommented:
Select * From dbo.PV_ITS_Global_FAQ Where question LIKE 'syntax' or answer like 'syntax'
(thats if both are in the same table...)
0
 
ktrimmerAuthor Commented:
MattisFlones,
I believe you are telling me to abandone the Freetext path and use a statement similar to

Select * From dbo.PV_ITS_Global_FAQ Where answer LIKE '%Kenny%';

IF that is the case, I do know that it does work, however, It does not search like I would like.

For instance, it would return three records from the example above.

But lets say I search for Kenny Mark (assuming mark was also in a record) It dosne't work
0
 
mattisflonesCommented:
Yes, thats what i would do.. I see youre talking about the webquery now, not in SQL.

Strange that it doesnt work.. it does for me! Never used the freetext, and never had use for it..
If you could state the kind of DB you use it would be easier to advice..
0
 
LowfatspreadCommented:
what happends if you seach for KEN instead?
0
 
ktrimmerAuthor Commented:
I'm using a ms sql 2000 database.

The table contains
id    cat          question          Answer
1     search     test                <p>kenny</p>     <p>Michelle</p>
2     search     test                 <p>kenny</p>    
3     search     test                  kenny

When using like statement,

 if I search for Kenny, it returns all three
IF I search for Kenny Michelle it only returns 1
If I search for Michelle Kenny, it returns None.

I hope this clears up the problem with why the LIKE statement doesn't work, IF  you know some magic, i more than happily try the LIKE statments again.

Although I would really like to be able to use the Full - Text Searching expcailly to use the CONTAINS
0
 
ktrimmerAuthor Commented:
sorry slip of the finger.

especially to use CONTAINS becuase it allows me to use bolean varialbe in my search terms.
0
 
ktrimmerAuthor Commented:
lowfatspread,
using the statment

Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'KEN');

Returns nothing


 
0
 
mattisflonesCommented:
This gives me all records...

SELECT *
FROM dbo.faq
WHERE Answer like '%kenny%' or Question like'%michelle%'

just tested on MSDE 2000

The context for contains is:
select * from dbo.faq where contains (*,'kenny')

Just rip the string down to single words, and make it search for each word in each collumn..
0
 
mattisflonesCommented:
If it doesn`t work theres something wrong with your index!
0
 
ktrimmerAuthor Commented:
The 'kenny' part will be replaced by a string that comes from a form. I guess I could write a bit of code that could tear apart the string at spaces and reconstruct a new sql statment based upon the number of words entered. . I will give it a try. However, to me it seems like a lot of work to do for something that should work already. Thanks for all of your help I am on my way out the door so I wont have anymore time today to look at this problem. I will catch up agin tomarrow.
0
 
ktrimmerAuthor Commented:
and by the way.

Select * From dbo.PV_ITS_Global_FAQ Where contains (*,'kenny');

Also only returns one record
0
 
ktrimmerAuthor Commented:
I dont beleve their is anything wrong with the index becasue, if I search for test in the Question column,

Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'test');

I get all three.
0
 
mattisflonesCommented:
Strange!
I would do the "string ripp" thing.. easier... in a way.. :-)
0
 
LowfatspreadCommented:
if you change one of the TESTs to <p>Test</p>
and search for test does it only return 2?

0
 
danblakeCommented:
It's all to do with how free text converts words into different word types: such as noisy words/word extensions/descriptors, as in this article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql7/html/filedatats.asp

0
 
Anthony PerkinsCommented:
In order for yout to get back the row containing "kenny2" when you search for "kenny" you must use the prefix form of the CONTAINS clause, as follows:

SELECT * FROM dbo.PV_ITS_Global_FAQ WHERE CONTAINS(*,'"Kenny*"')

Notice the double quotes within the single quotes.

There is no prefix search in FREETEXT.

Also, please return to this old open question:
http://www.experts-exchange.com/Web/Web_Languages/ASP/Q_20828976.html
0
 
ktrimmerAuthor Commented:
Well Thanks to you all for hanging in there over the weekend.
Okay this is the conclusion I am coming to.  I must confess that I don't like it. But here it goes.  Apparently, this freetext thing is not working. So, Therefore, I am about to abandon it. Perhaps it a setting somewhere that I have missed but I don't believe so. I believe the problem is stemming from how Freetext in accordance with the Full-Text search in SQL works. From what I can figure, and I could be quite off the mark on it, the Full-Text search indexes the columns in your database. It looks at the text and tries to make sense of it. In doing so, it throws out what's called noisy Words. ( From Danblakes Link) Apparently, becasue most of the text that is stored in this particular database i am createing, is enclosed in html tags, it picks them out as Noisy Words. I will admit that this is purly a theory and I can not prove it. However, to dispute this theory, I still can't figure out why other aspects of FreeText is not working. From the documentation i have been reading, all of the statements we have been testing should work. But the are not.

Acperkins SQL statment according to SQL Doc's should work but it is not. Likewise with Mattisflones.

What I do noitce is that the Fulltext is only matching exact wording. Including Case. This type of search is doing me no good.

So with that all said, I am reverting back to using the Like clause as mentioned above. I will leave this question open for a while to see if someone discovers the answer.

Thanks again for all of the  Help
0
 
danblakeCommented:
Also good demo setup of the various freetext types and what they do:
http://www.eggheadcafe.com/articles/20010422.asp

FREETEXT:
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition.

Such as candy would search for candies for example - BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2juc.asp
0
 
ktrimmerAuthor Commented:
Danblake,
I appricate your help. IF you notice the orginal posted sql statement.

Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'Kenny');

Does conform to the syntax in the links you have posted. The problem is that it is not returning the correct information. So, my question is why if my Freetext qurery not returning what the freetext doucmentation says it should.
0
 
mattisflonesCommented:
Did you try running "Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'Kenny')" in query analyzer?
0
 
Anthony PerkinsCommented:
Perhaps we can put this subject to rest by a sample.
1.  I created a table (Table1) with this structure.
CREATE TABLE [Table1] (
      [ID] [int] IDENTITY (1, 1) NOT NULL ,
      [Question] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [Answer] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

2. I created a full text search on the Question and Answer columns

3. I ran the following queries:
select * from table1
select * from table1 where freetext(*, 'kenny')
select * from table1 where contains(*, '"kenny*"')
Select * from table1 where contains(*, 'formsof(inflectional, kenny)')
Select * from table1 where Answer like '%kenny%'

And got this response:
ID          Question                                           Answer                                            
----------- -------------------------------------------------- --------------------------------------------------
1           Test                                               <P>Kenny</P>
2           Test                                               Kenny
3           Test                                               Kenny2
4           Test                                               <p>kenny</p>     <p>Michelle</p>
5           Test                                               <p>kenny</p>    
6           Test                                               kennys

(6 row(s) affected)

ID          Question                                           Answer                                            
----------- -------------------------------------------------- --------------------------------------------------
6           Test                                               kennys
2           Test                                               Kenny

(2 row(s) affected)

ID          Question                                           Answer                                            
----------- -------------------------------------------------- --------------------------------------------------
6           Test                                               kennys
3           Test                                               Kenny2
2           Test                                               Kenny

(3 row(s) affected)

ID          Question                                           Answer                                            
----------- -------------------------------------------------- --------------------------------------------------
6           Test                                               kennys
2           Test                                               Kenny

(2 row(s) affected)

ID          Question                                           Answer                                            
----------- -------------------------------------------------- --------------------------------------------------
1           Test                                               <P>Kenny</P>
2           Test                                               Kenny
3           Test                                               Kenny2
4           Test                                               <p>kenny</p>     <p>Michelle</p>
5           Test                                               <p>kenny</p>    
6           Test                                               kennys

(6 row(s) affected)

I trust that clarifies how FREETEXT, CONTAINS and LIKE work.

>>Acperkins SQL statment according to SQL Doc's should work but it is not.<<
It really depends on your definition of working.  I see that it is working, at least according to the definition of a Full Text Search.  But as has already been pointed out you can always use LIKE, just be prepared for the performance hit with large tables.
0
 
danblakeCommented:
Does conform to the syntax in the links you have posted. The problem is that it is not returning the correct information. So, my question is why if my Freetext qurery not returning what the freetext doucmentation says it should.

---------- Freetext is returning what the documentation says is should return:
FREETEXT:
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition.

Kenny vs Kenny2 is treated as a seperate entity.  Bit like King Richard and King Richard2 and King Richard 2.  The rules are difficult to generate a search facility of this type.  Richard2 will be handled differently, as the freetext engine treats this as a seperate and different word that means something completly different.
This can be confirmed by using the freetexttable, and getting the ranking of all matching records.

The problem here, as I have eluded to is how do you define other words that mean the same thing. Candy -> Candies for example is where freetext searching performs well.  In your case to search between strings of data with <p>%word%</p> it cannot be performed as a freetext search.  The <p> </p> is enough for the freetext search to believe it has a completly different meaning.  IF <letters> and </letters> were handled as noisy words (which they are in your particular case) we would not have a problem, but freetext searching does not handle these as noisy words.

Unfortunatly, you seem to be trying to force a feature to work the way you expect, rather than using the features to perform how you require it to perform.  (Or there is a gap between expectations and reality of the features).

0
 
ktrimmerAuthor Commented:
Once again thanks to you all for your imput.

Danblake,
I believe I would agree with you on the terms that becuase of the HTML tags, many of my words in the database are being picked up as Noisy. However, to dispute that, Why did it work for acperkins above.

So, when looking at acperkins posting, what I did notice is that the columns acperkins define have a type of varchar. My columns are defined as TEXT.

Danblake stated:
FREETEXT:
Is a predicate used to search columns containing character-based data types for values that match the meaning and not the exact wording of the words in the search condition.

Is the TEXT datatype considered to be Character-Based??

Also Acperkins had.

COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,

honestly, I don't really no what that means so I don't know if it will make a difference.

In the mean time, I did create a work around using a like statement here's the code of the function I used to create the SQL statement incase anyones interested it may be quick and dirty but I needed to move on with this project. But I still would rather use the freetext search.

      Function DisplaySQL ()
            Dim strQuerystring as String
            Dim strSQL as string
            Dim SearchString0 as String
            Dim SearchString1 as String
            Dim SearchString2 as String
      
            SearchString0 = Trim(request.params("txtSearch"))
            searchString1 =  Replace(SearchString0," ","%' Or Answer LIKE '%")
            SearchString1 = "Answer LIKE '%" & Searchstring1 & "%'"

            searchString2 =  Replace(SearchString0," ","%' Or Question LIKE '%")
            SearchString2 = " or Question  LIKE '%" & Searchstring2 & "%'"            
            
            'Response.Write("Search String" & searchstring1 & searchstring2)
            strQuerystring = request.QueryString("T")
            
            if strQuerystring="s" Then
                  strSQL = "SELECT * FROM dbo.PV_ITS_Global_FAQ WHERE " & SearchString1 & searchstring2
                  Else strSQL = "SELECT * FROM dbo.PV_ITS_Global_FAQ WHERE Cat = @Cat"
            End if
            Return strSQL
      End Function

Thanks again to all

0
 
danblakeCommented:
Note AcPerkins only picked up the HTML Tags when running:

Select * from table1 where Answer like '%kenny%'

or select * from table1

The difference of text/varchar/collation will not be an issue here.


Is the TEXT datatype considered to be Character-Based??
Yes
0
 
Anthony PerkinsCommented:
I agree with Dan absolutely.  The key in Full-Text Search is that it looks for words, so that it is no way that it is going to pick up:
 <P>Kenny</P>
When searching for:
Kenny
0
 
ktrimmerAuthor Commented:
Sorry for the misquote on ACperkins.

I guess this "  The key in Full-Text Search is that it looks for words, so that it is no way that it is going to pick up:
 <P>Kenny</P>When searching for: Kenny" was exactly what I was looking for.

On another note. Once I populated my database with "Real" data meaning the actual questions and answers, The freetext search was more reliable and accurate.

I believe Danblake stated my problem correctly

Unfortunatly, you seem to be trying to force a feature to work the way you expect, rather than using the features to perform how you require it to perform.  (Or there is a gap between expectations and reality of the features).

Thank you all for helping to correct my idiotness.
0
 
danblakeCommented:
I have worked out how to solve this problem, I knew there must be an answer, anyways this is how it goes:

In Program Files\Microsoft SQL Server\Mssql\Ftdata is the full-text search noise words file in a file called: noise.eng (or performing a search will find this file if not using the standard installation directories).

What we can do, is modify the features defaults to add : <p> </p> as a noisy word and all your other html/xml tags.  The file is stored in ASCII and can be modified in notepad.  By adding these words, they will not form part of the search index phrases so: <p>Kenny </p> will be picked up on a freetext search of Kenny.

If all the numbers 0, 1, 2,3,4,..9 are also added : Kenny2 will be picked up on  a freetext search of Kenny.  

This should now enable you to run this as a freetext search service as you require.
0
 
danblakeCommented:
You need to rebuild the freetext search catalogues after repopulating/changing noise.eng to ensure that this information is then used in the rebuilding of the search indexes.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 13
  • 9
  • 6
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now