SQL Server 2005 full-text search

Question

I got the SQL Server 2008 developer version, and this is my data:

 if exists (select * from dbo.sysobjects where id = object_id(N'test') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table test
    create table test
    (
    Id INT IDENTITY NOT NULL primary key,
    data NVARCHAR(255) not null
    )

    insert into test (data) values ('Hello world');
    insert into test (data) values ('Hello j-world');

I would like to find all the lines containing the j-world, while avoiding LIKE for efficiency reasons.

If I try:

select 
    * 
from test
where freetext
(
    *,
    N'j-world'
);

I get all the lines that are wrong. Should I implement my own word breaker or something else? Can I use iFTS in this situation at all?

Thank.

Christian

PS:

Let me ask my question more generally. How to find portable words using FTS (j-world is just an example)?

Answer 1

, FTS? , LIKE:

• SELECT * FROM test
  
  LIKE '% world%'
  • -
    
    -- !
• SELECT * FROM test
  
  LIKE '% j-world%'
  • -
    
    --Hello j-world!

---

FTS. () -

• SQL Server 2005, , 2008 .
  , 100 (.. SQL Server 2008).
  , 2005 .

SSMS\YourDatabaseName\\ - → " -...". vgvStoplist , " syoplist".

SSMS dbo.test --- > → --- > : , . → (I vgvStoplist)

* (, "j-world" ')

"Hello j-world" ( "Hello world" )

TSQL. msdn

---

==== :
, , .

, "j" - - (. - (*) "j" (3 ), . (**)), "-" - , -, wordbreaker.

. "" .
, , . , .

-, .
, .

• (*) script -
  -, " ", script ( "..." " " ),

script () find-and-replace / copy & paste from().

(**) , vgv_sys_copy, FT StopList:

ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'French';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'Italian';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'Japanese';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'Dutch';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'Russian';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'Swedish';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'Simplified Chinese';
ALTER FULLTEXT STOPLIST [vgv_sys_copy] ADD 'j' LANGUAGE 'British English';

Update2
subquestion Performace FTS LIKE (-)?

, , , SQL Server 2005
MSSQL\FTData\noiseENG.txt, Noise Words Sql Server 2005

'j'. , , noiseENG.txt . .

, . StackExchange ( SF) . , . FAQ.

Answer 2

CONTAINS, ? Freetext "LIKE".

Answer 3

FREETEXT is less accurate than CONTAINS

http://www.mssqltips.com/tip.asp?tip=1382

CONTAINS:

http://msdn.microsoft.com/en-us/library/ms187787.aspx

Answer 4

I don’t have a database installed in the FTS for testing, but have you tried something like contains (data, 'world AND NOT "-world?')? You may have to study the modification of the phrase.