February 9, 2006 at 9:48 am
I just migrated a dev db to 2005. Built a new free text catalog and executed a standard set of queries that I have against it. The results are not what I expected.
Right now in an employees table I index first name, last name and middle initial into a catalog for the sole reason that, our employees want to be able to search on 'John Smith' and find a match. Now I can write a function that rips apart this string and do a bunch of LIKEs to search for each of the words in firstname, lastname and middle initial, but the the full text catalog in SQL 2000 did this for me with the FREETEXT predicate.
SQL 2005 doesn't return the same results and books online doesn't differ in syntax. I know the entry is there because if I execute it against just the firstname, the result comes up.
This is the statement that doesn't work:
select * from employees where freetext(employees.*,' "John Smith" ')
Yet, these next statements work so I know the catalog is populated and the Full Text is operational:
select
* from employees where freetext(employees.*,' "Smith" ')
or
select
* from employees where freetext(employees .*,' "John" ')
Any ideas?
February 10, 2006 at 8:36 am
Chris,
Your first query [select * from employees where freetext(employees.*,' "John Smith" ')] is a "phrase" search for the specific string "John Smith". Does this exact string exist in one column in the employees table?
Your other FREETEXT queries will search for "Smith" or "John" in any FT-enabled column in the employees table.
Hope that helps,
John
SQL 2005 Full Text Search Blog
John T. Kane
February 10, 2006 at 8:46 am
John,
No, it does not exist in one column. When I created the FT Catalog I selected firstname, lastname and middle initial to be indexed and assumed the engine would be able to pick out the values and identify John Smith.
It does this in 2000...just not in 2005.
I guess as a workaround I can create a fullname column when the employee is inserted and index that for searching. It just doesn't seem right that if I place those three columns in one catalog, that I can't search on terms that can exist in more than one or an aggregate of them...isn't that the point of creating a catalog? Otherwise there is not that much advantage over using a LIKE operator....
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply