November 29, 2005 at 1:59 pm
I'm using sql Server 2000 and trying to do some searches and have come across a problem. I have a client Name field that I'm preforming my search on. I've stored the string "A.B. & C." in the field. I use the following select statement:
select * from table where freetext (cl_name, '"ABC"')
I get an empty reslut set back. I've tried "AB C", "AB", "A" with no sucess. Any help or suggestions would be very much appreciated.
November 30, 2005 at 6:38 am
I think you have one problem with the query and one change I can't yet explain.
The problem is searching for a string 'ABC' which does not exist in the field. The value in the field includes periods and the & character. You will need to add some wildcards, a regular expression, or multiple WHERE conditions to achieve what you want.
Now for the part I can't explain yet (I just started looking at full text searching yesterday). Try the predicate CONTAINS rather than FREETEXT.
select * from table where contains (cl_name, '"A*"')
Hopefully someone experienced with full test search will respond with some insight here. My (currently limited) understanding is that FREETEXT looks at the meaning of a word rather than simply its characters. Maybe I search for 'run' and it finds 'ran', 'running', etc. CONTAINS allows for more complex syntax.
In the next few days, I am going to check out BOL and Google for some clarification.
Hope this helps.
November 30, 2005 at 6:41 am
In my earlier response, I should also have mentioned the need to repopulate the index after any data changes. The first few times I added records I couldn't find them with FTS. Then remembered that in SQL Server 2000 the index is not populated/updated until it is explicitly repopulated.
November 30, 2005 at 7:01 am
If that is the true text then sql will treat A,B and C as ignored words and you will only be able to use CONTAINS
select * from
where CONTAINS(cl_name, '"A.B. & C."')
However if the text did not contain ignored words such as "Tom.Dick & Harry" then FREETABLE will work with
select * from
where FREETABLE(cl_name, '"Tom Dick Harry"')
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply