May 10, 2005 at 12:14 am
Hello All,
We are using SQL Server 2000 as our database. We have implemented full text catalog for the free text searching utility in our application. While testing the implementation of the mentioned catalog based search, we found that the search mechanism igoners '_' (underscore) when specified as the first character of search text. For example, search result for "_option" returns result for "option" and "_" is ignored when there is a valid content that has occurences of "_option" or "_new".
We also believe that SQL server catalog based search does not consider "_" as either a Punctuation symbol or a Word breaker.
We are not able to figure out why is the search result for "_option" is returning result for only "option" and ignoring the content "_option". We would appreciate a help on this matter.
For more details on this matter, When we run any one of the following query, we get the same results.
Query-1:
----------
SELECT *
FROM searchtable
WHERE CONTAINS(freetext, '"_option*"')
Query-2:
----------
SELECT *
FROM searchtable
WHERE CONTAINS(freetext, '"option*"')
May 13, 2005 at 8:00 am
This was removed by the editor as SPAM
May 13, 2005 at 8:50 am
I cannot test this as I don't have it implemented on my machine but here're a few suggestions after going through BOL...let me know if any of them do the trick....
1) SELECT *
FROM searchtable
WHERE CONTAINS(freetext, 'option*')
2) SELECT *
FROM searchtable
WHERE CONTAINS(freetext, '"_option*"')
3) SELECT *
FROM searchtable
WHERE CONTAINS(freetext, '_ NEAR option')
**ASCII stupid question, get a stupid ANSI !!!**
May 13, 2005 at 9:21 am
I know that this is with a LIKE expression but I thought it was interesting (came across this in one of my Googles and have lost the site url) and it may help you rephrase your syntax for the underscore...
"Also are you aware that an underscore in a LIKE expression will match any single character - i.e. 'HW_%' will match e.g. 'HWxxx' as well as 'HW_xxx'. To find tables whose name begins with 'HW_', you need to escape the underscore: ... LIKE 'HW[_]%'"
**ASCII stupid question, get a stupid ANSI !!!**
May 13, 2005 at 9:27 am
& this from MS website related to underscore....maybe you just need to upgrade your MDAC version ....worth a shot...
"Calling the ODBC API SQLTables() function on a SQL Server database name that contains an underscore (for example, "my_db") may not return any of the table names contained in the database.
To resolve this problem, obtain the latest service pack for Microsoft Data Access Components 2.6"
**ASCII stupid question, get a stupid ANSI !!!**
May 13, 2005 at 12:20 pm
Hello. I'm not sure why freetext is in the column position of the example but a FREETEXT full-text search searches for a close match, not an exact match. More information about CONTAINS and FREETEXT can be found:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2y2h.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_2juc.asp
Everett Wilson
ewilson10@yahoo.com
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply