Solved

FreeText Search Doesn't Work

Posted on 2004-03-26
35
728 Views
Last Modified: 2008-02-01
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
Comment
Question by:ktrimmer
  • 13
  • 9
  • 6
  • +3
35 Comments
 
LVL 15

Expert Comment

by:mattisflones
ID: 10690257
where freetext like 'kenny'
0
 
LVL 15

Expert Comment

by:mattisflones
ID: 10690263
where coloumn like 'syntax' that is..
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690474
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10690483
which language have you enabled as the default for fulltext...
which collation sequences do the columns have
0
 
LVL 15

Expert Comment

by:mattisflones
ID: 10690497
Youll need Where COLOUMNNAME like 'syntax' (try just 'kenny' first, dont know about *,' ')
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690508
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
 
LVL 15

Expert Comment

by:mattisflones
ID: 10690530
Select * From dbo.PV_ITS_Global_FAQ Where question LIKE 'syntax' or answer like 'syntax'
(thats if both are in the same table...)
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690609
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
 
LVL 15

Expert Comment

by:mattisflones
ID: 10690654
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10690753
what happends if you seach for KEN instead?
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690759
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
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690771
sorry slip of the finger.

especially to use CONTAINS becuase it allows me to use bolean varialbe in my search terms.
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690842
lowfatspread,
using the statment

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

Returns nothing


 
0
 
LVL 15

Assisted Solution

by:mattisflones
mattisflones earned 100 total points
ID: 10690935
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
 
LVL 15

Expert Comment

by:mattisflones
ID: 10690982
If it doesn`t work theres something wrong with your index!
0
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690988
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
 
LVL 1

Author Comment

by:ktrimmer
ID: 10690994
and by the way.

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

Also only returns one record
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:ktrimmer
ID: 10691009
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
 
LVL 15

Expert Comment

by:mattisflones
ID: 10691017
Strange!
I would do the "string ripp" thing.. easier... in a way.. :-)
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 10691035
if you change one of the TESTs to <p>Test</p>
and search for test does it only return 2?

0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 10692651
0
 
LVL 13

Expert Comment

by:danblake
ID: 10694223
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10701128
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
 
LVL 1

Author Comment

by:ktrimmer
ID: 10704827
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
 
LVL 13

Expert Comment

by:danblake
ID: 10707056
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
 
LVL 1

Author Comment

by:ktrimmer
ID: 10707374
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
 
LVL 15

Expert Comment

by:mattisflones
ID: 10708623
Did you try running "Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'Kenny')" in query analyzer?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10710050
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
 
LVL 13

Accepted Solution

by:
danblake earned 100 total points
ID: 10711217
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
 
LVL 1

Author Comment

by:ktrimmer
ID: 10713203
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
 
LVL 13

Expert Comment

by:danblake
ID: 10713223
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 100 total points
ID: 10713352
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
 
LVL 1

Author Comment

by:ktrimmer
ID: 10713423
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
 
LVL 13

Expert Comment

by:danblake
ID: 10755921
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
 
LVL 13

Expert Comment

by:danblake
ID: 10755934
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now