Full-Text Search with single characters

Question

Full-Text Search with single characters

Jeremy-278203

SSC Veteran

Points: 272
More actions
November 16, 2005 at 12:07 pm

#102935

I am running full-text search on SQL Server 2000. I need to be able to search on single characters (they are product names in some cases) but even after removing the single characters from each noise word file (I found 3 seperate copies on the server) I still get the "query contained only ignored words" error. I am using a neutral word breaker on the column. Any help would be much appreciated.
Thanks.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Kane SSCrazy Points: 2810 More actions · Answer 1

Jeremy,

This is somewhat a FAQ on the public fulltext newsgroup, and for SQL Server 7.0 or 2000, the version of SQL Server is less useful than the OS platform you have it installed on. See the results from this link for more info on this topic: http://groups.google.com/groups?q=langwrbk+infosoft

Could you post the full output of the following SQL code?

use <your_database_name_here>

go

SELECT @@language

SELECT @@version

EXEC sp_help_fulltext_catalogs

EXEC sp_help_fulltext_tables

EXEC sp_help_fulltext_columns

EXEC sp_help <your_FT-enable_table_name_here>

go

-- the exact CONTAINS or FREETEXT query you are using with expected results..

For now, lets assume that you problem is with the single letter "C" as in the programming language... If so, then did you removed the single letter "C" from the noise.enu (US English) or noise.dat (Neutral) file located under \FTDATA\SQLServer\Config where your SQL Server 2000 is installed. If so, have you also run a Full Population after removing this single letter? Did you just remove all single letters, or did you empty the noise.* file?

For now, I'm going to assume that you're using SQL Server 2000 installed on Win2K (Windows 2000 Server), SQL FTS is using the OS-supplied wordbreaker dll - infosoft.dll. In your environment, you will encounter the following results:

<Windows 2000 Server results ONLY>

- FREETEXT will find C++, c#, C-, but NOT C without Error 7619 on Win2K SP4

with SQL2K SP3 when C is in noise.enu.

- CONTAINS will find C++ c# C- but Error 7619 with C on Win2K SP4 with SQL2K

SP3 when C is in noise.enu.

- FREETEXT will find all rows contain C regardless of punctuation on Win2K SP4

with SQL2K SP3 when C is NOT in noise.enu.

- CONTAINS will find all rows contain C regardless of punctuation on Win2K SP4

with SQL2K SP3 when C is NOT in noise.enu.

<\Windows 2000 Server results ONLY>

If you have further questions, please post your exact SQL query, search word, and sample data.

Regards,

John

SQL Full Text Search Blog

http://spaces.msn.com/members/jtkane/

John T. Kane

Jeremy-278203 SSC Veteran Points: 272 More actions · Answer 2

John,

Thanks for taking the time to answer my question. I have emptied out all of the noise.* files I could find (including noise.dat, noise.enu, noise.eng) in all 3 locations on the server. I also recreated the catalog and ran a full population. I read on another post that you suggested inserting a single space character back into the noise.dat file. I will try this right now. Thanks again for your help.

Here is the result of the query you posted:

us_english

5 ERefCatalog C:\Program Files\Microsoft SQL Server\MSSQL\FTData 0 2

dbo I2Comm PK_I2Comm 1 1 ERefCatalog

dbo I2Pricesvc PK_I2Pricesvc 2 1 ERefCatalog

dbo 2062630391 I2Comm COMM_DESC 5 NULL NULL 0

dbo 2142630676 I2Pricesvc SEARCH_KEYWORD 82 NULL NULL 0

I2Pricesvc dbo user table 2005-11-07 13:01:04.750

ID int no 4 10 0 no (n/a) (n/a) NULL

PS_PIK numeric no 9 15 0 no (n/a) (n/a) NULL

SEARCH_KEYWORD nvarchar no 8000 yes (n/a) (n/a) SQL_Latin1_General_CP1_CI_AS

(truncated)

ID 1 1 0

No rowguidcol column defined.

PRIMARY

Idx_Comm_Pik nonclustered located on PRIMARY COMM_PIK

Idx_I2_Cat_Num nonclustered located on PRIMARY I2_CAT_NUM

Idx_Item_Pik nonclustered located on PRIMARY ITEM_PIK

Idx_ItemID nonclustered located on PRIMARY ID

Idx_Leaf_Class nonclustered located on PRIMARY LEAF_CLASS

Idx_Mfr_Cat_Num nonclustered located on PRIMARY MFR_CAT_NUM

Idx_Mfr_Desc nonclustered located on PRIMARY MFR_DESCRIPTION

Idx_Mfr_FullName nonclustered located on PRIMARY MFR_FULLNAME

PK_I2Pricesvc clustered, unique, primary key located on PRIMARY PS_PIK

PRIMARY KEY (clustered) PK_I2Pricesvc (n/a) (n/a) (n/a) (n/a) PS_PIK

Here is a sample query and a subset of the expected results:

select * from i2pricesvc i2ps, manufacturer m where

i2ps.mfr_fullname = m.manufacturername and contains(search_keyword, 'K and FRAME')

I realize I could search for "K FRAME" instead and that works, but there are cases where the single character is not part of a phrase.

968823 ELECTRONIC, MOLDED CASE; K FRAME; 240/480/600 VAC AT 50/60 HZ (UL/CSA/NEMA); 240/380/415 VAC AT 50/60 HZ (IEC); 250 VDC (UL/CSA/NEMA/IEC); 125 AMPERE AT 40 DEG C; 3 POLE; INTERRUPTING RATING 100 KILOAMPERE AT 240 VAC, 65 KILOAMPERE AT 480 VAC, 35 KILOAMPER

968826 ELECTRONIC, MOLDED CASE; K FRAME; 240/480/600 VAC AT 50/60 HZ (UL/CSA/NEMA); 240/380/415 VAC AT 50/60 HZ (IEC); 250 VDC (UL/CSA/NEMA/IEC); 125 AMPERE AT 40 DEG C; 3 POLE; INTERRUPTING RATING 100 KILOAMPERE AT 240 VAC, 65 KILOAMPERE AT 480 VAC, 35 KILOAMPER

966053 ELECTRONIC, MOLDED CASE; K FRAME; 240/480/600 VAC AT 50/60 HZ (UL/CSA/NEMA); 240/380/415 VAC AT 50/60 HZ (IEC); 250 VDC (UL/CSA/NEMA/IEC); 125 AMPERE AT 40 DEG C; 3 POLE; INTERRUPTING RATING 100 KILOAMPERE AT 240 VAC, 65 KILOAMPERE AT 480 VAC, 35 KILOAMPER

966071 ELECTRONIC, MOLDED CASE; K FRAME; 240/480/600 VAC AT 50/60 HZ (UL/CSA/NEMA); 240/380/415 VAC AT 50/60 HZ (IEC); 250 VDC (UL/CSA/NEMA/IEC); 125 AMPERE AT 40 DEG C; 3 POLE; INTERRUPTING RATING 100 KILOAMPERE AT 240 VAC, 65 KILOAMPERE AT 480 VAC, 35 KILOAMPER