Full Text Search - Decimal Point
-
I'm trying to index values like '3.0' using the Full Text Search.
If you are looking for a drill bit with a diameter of 3.0 mm or better 4.5 mm then you should be able to search using the diameter of the drill bit. I presume it has something to do with SQL Server limitations, that a user is not allowed to search for such values.
The single digits 0-9 are not allowed, which would explain why the search fails using the full text search value of 4.5 ... IF the decimal point is some kind of special character.
The Wordlists or noise lists do not contain the decimal point, which leaves me with the question:
How can I index and search for the value 4.5??
Thanks for any help.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Edited by - hot2use on 10/31/2003 06:46:01 AM
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Hi there
Consider:
a) replacing . with "." before applying the search
b) try removing . from the noise work for the language breaker being used
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Hi Ck
No "." in the noise.dat files (noise.enu, noise ena, noise.deu, etc.). Unless you meant something totaly different.
Will let you know if the trick with 3"."0 works. You see, I'm only the DBA and can't access the app. 😉
Cheers
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Here's just to let you know that it didn't work searching for 3"."0 and so I'm stuck where I started off from.
Anybody else got a cool suggestion?
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
Put quotes around text you want found
where contains(indexedcolumn,"3.0")
Far away is close at hand in the images of elsewhere.
Anon. -
whoops.. I ment this:
where contains(indexedcolumn,"3'.'0")
sorry about that.. cant read my own friggen scripts! 🙂
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Well i tried a manual search via SQL Query Analyzer using the following statement:
select ctx_desc from dbo.item_tl
where contains(ctx_desc,'3.0')
...and the system returned the following error:
Server: Msg 7619, Level 16, State 1, Line 3
Execution of a full-text operation failed. A clause of the query contained only ignored words.
It's got to be a problem with the decimal point. SQL Server ignores it completely although it isn't listed in the noise files.
I even tried the search string '3''.''0' (all single quotes) and got the same error message back. I then tried '"3.0"' and got the same error message.
Edited by - hot2use on 11/04/2003 12:46:27 AM
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
The query I posted worked fine for me, with single or double quotes.
However ckempste's does not?
What version of sql are you on, I'm on SLQ7 SP4.
Far away is close at hand in the images of elsewhere.
Anon. -
I'm running the English version of SQL 2000 SP2 (SQL Server 2000 SP2 2000.80.534.0 )
Single quotes work better via the Query Analyzer. Double quotes would be used if i'd be adding additional criteria. E.g.
CONTAINS(ctx_desc,'"Bohrer" AND "Hartmetall" AND "3.0"')
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
quote:
CONTAINS(ctx_desc,'"Bohrer" AND "Hartmetall" AND "3.0"')This is exactly how I use Full Text Search.
I wonder if SQL2K Full Text Search is different, I hope not as I have just developed a system with SQL7 and will be moving to SQL2K soon.
Could it have something to do with the collation used on the column?
Does anyone else have this problem with SQL2K?
Far away is close at hand in the images of elsewhere.
Anon. -
The collation on my table/column is <database default> which means I'm using SQL_Latin1_General_CP1_CI_AS.
The query I posted in my last post with the AND clauses, returns the error message:
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained only ignored words.
It is a problem with the decimal point. Point. 😉 I do hope that somebody has a solution.
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find. -
create table cktest_fti (id int identity(1,1), col2 varchar(50))
insert into cktest_fti(col2) values ('2.3')
insert into cktest_fti(col2) values ('2.4')
select *
from cktest_fti
where contains(*,'"2.3"')
==> finds the row
select *
from cktest_fti
where contains(*,'2"."3')
==> ignored words error
Sorry, my 1st post was supposed to be the '2"."3' senario, which does error. In my table I dont have 3.0, but have coded values such as ML22.34 which works fine, I can see why FTI is ignoring the combo above. Try ripping the values from the noise file but I cant guarantee it and may result in other issues for you later on.
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA" -
Created your table in the 'pubs' database on the same server where I'm having the problems. Rebuilt the FTI using the neutral word-breaker settings. Executed the select statements and received the same error message for both statements.
Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. A clause of the query contained only ignored words.
🙁
Regards
__________________________________
Searching the KB articles is like
picking your nose. You never know
what you'll find.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply