?
Solved

FreeText Search Doesn't Work

Posted on 2004-03-26
35
Medium Priority
?
742 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 400 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
 
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 43

Expert Comment

by:Eugene Z
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 400 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 400 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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

743 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