FreeText Search Doesn't Work

where freetext like 'kenny'

where coloumn like 'syntax' that is..

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,

which language have you enabled as the default for fulltext...
which collation sequences do the columns have

Youll need Where COLOUMNNAME like 'syntax' (try just 'kenny' first, dont know about *,' ')

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

Select * From dbo.PV_ITS_Global_FAQ Where question LIKE 'syntax' or answer like 'syntax'
(thats if both are in the same table...)

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

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..

what happends if you seach for KEN instead?

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

sorry slip of the finger.

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

lowfatspread,
using the statment

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

Returns nothing


 

If it doesn`t work theres something wrong with your index!

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.

and by the way.

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

Also only returns one record

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.

Strange!
I would do the "string ripp" thing.. easier... in a way.. :-)

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

try repopulate a Full-Text Index
see:
http://support.microsoft.com/default.aspx?scid=%2Fsupport%2Fsql%2FContent%2F2000Papers%2FFTS_White%2520Paper.asp

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

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:
https://www.experts-exchange.com/questions/20828976/ASP-Net-and-FullName.html

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

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

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.

Did you try running "Select * From dbo.PV_ITS_Global_FAQ Where Freetext(*,'Kenny')" in query analyzer?

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.

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

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

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.

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.

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.