August 16, 2004 at 8:53 am
Hi All, i have created a full text index on a table in my database. As i understand it a full text search will allow me to search for things such as (Target Word) = Username (Supplied Search Word) = user. And this will find the above target word. It will also supply me with a ranking as well to say which words match the best.
Ok, is this the correct understanding first of all.
Secondly, i have created an SP that is used to make use of the full text index search, The code for this is as follows.
alter PROCEDURE freetext_rank_proc
@select_list nvarchar(1000),
@from_table nvarchar(517),
@freetext_column sysname,
@freetext_search nvarchar(1000),
@additional_predicates nvarchar(500) = '',
@order_by_list nvarchar(500) = ''
AS
BEGIN
DECLARE @table_id integer,
@unique_key_col_name sysname,
@add_pred_var nvarchar(510),
@order_by_var nvarchar(510)
-- Get the name of the unique key column for this table.
SET @table_id = Object_Id(@from_table)
SET @unique_key_col_name =
Col_Name( @table_id,
ObjectProperty(@table_id, 'TableFullTextKeyColumn') )
-- If there is an additional_predicate, put AND() around it.
IF @additional_predicates <> ''
SET @add_pred_var = 'AND (' + @additional_predicates + ')'
ELSE
SET @add_pred_var = ''
-- Insert ORDER BY, if needed.
IF @order_by_list <> ''
SET @order_by_var = 'ORDER BY ' + @order_by_var
ELSE
SET @order_by_var = ''
-- Execute the SELECT statement.
EXECUTE ( 'SELECT '
+ @select_list
+ ' FROM '
+ @from_table
+ ' AS FT_TBL, freetexttable('
+ @from_table
+ ','
+ @freetext_column
+ ','''
+ @freetext_search
+ ''','+'100) AS KEY_TBL '
+ 'WHERE FT_TBL.'
+ @unique_key_col_name
+ ' = KEY_TBL. '
+ @add_pred_var
+ ' '
+ @order_by_var
)
END
I will eventually acces this from VB.NET, but for now am testing the functionality of this in Query Analyser. The code i am using to test this is as follows.
DECLARE @Order nvarchar(4000)
Set @Order = 'submittedBy' +','+'timeLogged'+','+'bugType'+','+'description'+','+'status'+','+'fixedBy'+','+'fixedDate'+','+'severity'
exec freetext_rank_proc @Order,'BugTrack','*','Test'--,'KEY_TBL.RANK >= 1','KEY_TBL.RANK DESC'
Now to the problem, If i have the rank and order uncommented in the SP call then the SP returns no results.
However when they are as above, and i search for Test this returns results containing 'Test' and 'testing', but not 'testing2'
and if i search for anything that is not the first phrase in a field then unless the search text is exact then no results are found, for example one description column contains 'Test User DavidN no 3'
if i search for Dav then no result is found, it is my understanding that this should find the field in this case.
I hope i understand this correcly and i have explained well enough.
Dave
P.S. Below is the tabe returned on the SP call i have supplied using Test
50 15/08/2004 2 Test User DavidN no 3 2 2 -1 2
50 16/08/2004 3 Test User DavidN no 4 3 2 -1 3
50 19/08/2004 0 Test User DavidN no 5 4 2 -1 0
50 16/08/2004 1 Test User DavidN no 2 1 2 -1 1
50 16/08/2004 0 Test User DavidN no. 1 0 50 -1 0
2 12/07/2004 0 Testing 0 50 12/07/2004 0
August 18, 2004 at 12:55 am
Last time I did Full Text was in SQL 7 about 5 years ago. But from what I remeber and BOL:
For the cases of
" search for Test this returns results containing 'Test' and 'testing', but not 'testing2'"
"i search for Dav then no result is found"
You needed to search for Dav* or Test* ... just like a dos wildcard. Maybe edit your sproc to put the asterix on the end of every word. (that should slow things down nicely for you).
As for the order by rank problem. Not sure without a catalog to test against. sorry!
Julian Kuiters
juliankuiters.id.au
August 18, 2004 at 2:08 am
That great thanks
August 18, 2004 at 9:41 am
You can also use FORMSOF(INFLECTIONAL) for test and testing as testing is a valid word variation to test, however, this will not work for testing2 and you will need to use the astrick as a wildcard.
In regards to the RANK values, you may want to use CONTAINSTABLE as FREETEXTTABLE ignores boolean conditions, such as AND or OR. Also, the number of rows in your FT-enable column is important, as the table needs to have statisticlly large number of rows for RANK to return valid numbers. See SQL Server 2000 BOL "Full-text Search Recommendations" for more info on RANK.
Additionally, the OS Platform (Win2K vs Win2003) can have an affect on the results that are returned based upon the actual textual data you are searching due to different wordbreakers (Win2K - infosoft.dll and Win2003 - langwrbk.dll).
Regards,
John T. Kane
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply