Full text search on TEXT field

I've a problem. I'm inexperienced about FTS.
I have a TEXT datatype field in my table and I store MS Word documents.

I want to do full text search on this TEXT datatype field.
So I build catalog and run

"Select * from table where Contains (textfield,'Yusuf')" sql in analyzer.

but null value returns. however I know the table has 3 records.

Anyway, I insert a varchar field in the table and edit my fts catalog.
and then insert 3 records in the table.than rebuild catalog and I try
"Select * from table where Contains (varcharfield,'Yusuf')"
this sql returns true records.

I know that I can FTS in TEXT datatype field but I can't. I don't know why.
I need your help.
thanks alot

YusufBesides,
I insert text in text datatype field and returs true values...

The problem occours when insert only MS Word or MS Excel etc files.

Full Text Indexing

I need to store documents in a central location and search them using the features associated with Microsoft Search Service. I can either store them on SQL Server or on the server direct - using either method I can retrieve them using sql (in the case of the later I need to use the openquery method and deploy a linked server).
I am trying to work out which method I should use, at the moment I am inclined towards storing them outside the database as this seems to be quicker. Any advice about the pros and cons associated with each would be much appreciated.Dave,
Well, if you need to search them via the "Microsoft Search Service" and
you're using SQL Server 2000, then you must store the files in SQL Server in
column defined as an IMAGE datatype along with a "binding" column for the
file extension. See SQL Server 2000 Books Online (BOL) title "Filtering
Supported File Types" for more information. Note, this is a new feature of
SQL Server 2000 Full-Text Search (FTS).
If you do not have SQL Server 2000 and are using SQL Server 7.0, you can
store the files on disk with pointers to the doc in a SQL Server table and
then use the "Indexing Service" to FT index the documents and use a
openquery method and deploy a linked server. However, you will find that
compared to storing the files in SQL Server 2000, that this option will not
perform as well.
There are other considerations that may also affect your decision to store
the docs inside or outside the database, such as the number of files, (just
thousands, or millions?) as well as security/transaction control (on disk,
anyone can add, delete or modify the files). There are many factors that can
and do enter into this discussion as I've seen flame wars started over this
issue, so more information on exactly what your requirements are would be
helpful in making this decision...
Note, you can also post FTS related questions to the newsgroup:
microsoft.public.sqlserver.fulltext
Regards,
John
"Dave Walsh" <anonymous@.discussions.microsoft.com> wrote in message
news:41FEB598-76FA-4D1F-9122-144F06922AD5@.microsoft.com...
> I need to store documents in a central location and search them using the
features associated with Microsoft Search Service. I can either store them
on SQL Server or on the server direct - using either method I can retrieve
them using sql (in the case of the later I need to use the openquery method
and deploy a linked server).
> I am trying to work out which method I should use, at the moment I am
inclined towards storing them outside the database as this seems to be
quicker. Any advice about the pros and cons associated with each would be
much appreciated.

FTS is accent sensitive?

Hi,
I'm planning to store some romanian documents in SQL image. I understand
that I have to use neutral language.
What I need to know if FTS is accent sensitive: some peoples writes
"acas"(means home), others simply "acasa".
The query "acasa" returns "acasa" and "acas" or only "acasa".
Sorry for my bad english..
Thank you.
Best regards,
Emil Mustea
It is not accent sensitive, or accent aware. searches on "acas" will not
match on row containing "acasa".
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:%239GG17WKEHA.2576@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm planning to store some romanian documents in SQL image. I understand
> that I have to use neutral language.
> What I need to know if FTS is accent sensitive: some peoples writes
> "acas"(means home), others simply "acasa".
> The query "acasa" returns "acasa" and "acas" or only "acasa".
> Sorry for my bad english..
> Thank you.
> Best regards,
> Emil Mustea
>
|||> It is not accent sensitive, or accent aware. searches on "acas" will not
> match on row containing "acasa".
So it's true: searches on "acasa" will return both "acasa" and
"acas".Correct?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:uZbXCiYKEHA.3316@.tk2msftngp13.phx.gbl...
> It is not accent sensitive, or accent aware. searches on "acas" will not
> match on row containing "acasa".
> "Emil Mustea" <emil@.sonic.ro> wrote in message
> news:%239GG17WKEHA.2576@.TK2MSFTNGP12.phx.gbl...
>
|||Emil,
The answer to your question for SQL Server 2000 (RTM to current SP3a) is no.
I've tested this with both an accent sensitive database and an accent
insensitive database collation using the same data and same server
configuration:
select TextCol, VarcharCol, CharCol from FTSAccent
/*
TextCol VarcharCol CharCol
-- -- --
Kahla HalfPipe jam caf cafe
Kahlua HalfPipe jam cafe cafe
Halfpipe Jam? classic-recipes CD-R
Halfpipe Jam classic recipes CD R
*/
-- Accent testing for "accent insensitive" results
select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
non-accented word
-- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
jam" as this database is accent-Insensitive
-- Actual Results : 1 row - "Kahlua HalfPipe jam"
select TextCol from FTSAccent where contains(TextCol,'Kahla') -- accented
word
-- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
jam" as this database is accent-Insensitive
-- Actual Results : 1 row - "Kahla HalfPipe jam"
-- Accent specific testing for "accent sensitive" results...
select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
non-accented word
-- Expected Returns: 1 row - "Kahlua HalfPipe jam" and NOT "Kahla HalfPipe
jam" as this database is accent-sensitive
-- Actual Results : 1 row - "Kahlua HalfPipe jam"
select TextCol from FTSAccent where contains(TextCol,'Kahla') -- accented
word
-- Expected Returns: 1 row - "Kahla HalfPipe jam" and NOT "Kahlua HalfPipe
jam" as this database is accent-sensitive
-- Actual Results : 1 row - "Kahlua HalfPipe jam"
As you can see the actual results differs from the expected results with
both accent sensitive and accent insensitive database collations and for the
accent insensitive database collation, only the accented or non-accented
search word was returned, but not both. This may or may not be fixed in a
future service pack for SQL Server 2000.
Additionally, for SQL Server 2005 (codename Yukon) will support accent
sensitive or insensitive fulltext search via new T-SQL:
CREATE FULLTEXT CATALOG fulltext_catalog_identifier ON FILEGROUP
filegroup_identifier
IN PATH <root path> WITH ACCENT SENSITIVE | INSENSITIVE AS DEFAULT
Regards,
John
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:OP22HkgKEHA.3392@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
not[vbcol=seagreen]
> So it's true: searches on "acasa" will return both "acasa" and
> "acas".Correct?
>
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:uZbXCiYKEHA.3316@.tk2msftngp13.phx.gbl...
not[vbcol=seagreen]
understand
>
|||Thank you for your detailed reply.
In Romanian we have a 2 letters with accents in the bottom of the letter
""(means "sh" in english) ""(it's sounds like zz in Pizza) . These letters
will be considered accented?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uLIxb5hKEHA.3216@.tk2msftngp13.phx.gbl...
> Emil,
> The answer to your question for SQL Server 2000 (RTM to current SP3a) is
no.
> I've tested this with both an accent sensitive database and an accent
> insensitive database collation using the same data and same server
> configuration:
> select TextCol, VarcharCol, CharCol from FTSAccent
> /*
> TextCol VarcharCol CharCol
> -- -- --
--
> --
> Kahla HalfPipe jam caf cafe
> Kahlua HalfPipe jam cafe cafe
> Halfpipe Jam? classic-recipes CD-R
> Halfpipe Jam classic recipes CD R
> */
> -- Accent testing for "accent insensitive" results
> select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
> non-accented word
> -- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
> jam" as this database is accent-Insensitive
> -- Actual Results : 1 row - "Kahlua HalfPipe jam"
> select TextCol from FTSAccent where contains(TextCol,'Kahla') --
accented
> word
> -- Expected Returns: 2 rows - "Kahlua HalfPipe jam" and "Kahla HalfPipe
> jam" as this database is accent-Insensitive
> -- Actual Results : 1 row - "Kahla HalfPipe jam"
>
> -- Accent specific testing for "accent sensitive" results...
> select TextCol from FTSAccent where contains(TextCol,'Kahlua') --
> non-accented word
> -- Expected Returns: 1 row - "Kahlua HalfPipe jam" and NOT "Kahla
HalfPipe
> jam" as this database is accent-sensitive
> -- Actual Results : 1 row - "Kahlua HalfPipe jam"
> select TextCol from FTSAccent where contains(TextCol,'Kahla') --
accented
> word
> -- Expected Returns: 1 row - "Kahla HalfPipe jam" and NOT "Kahlua
HalfPipe
> jam" as this database is accent-sensitive
> -- Actual Results : 1 row - "Kahlua HalfPipe jam"
> As you can see the actual results differs from the expected results with
> both accent sensitive and accent insensitive database collations and for
the
> accent insensitive database collation, only the accented or non-accented
> search word was returned, but not both. This may or may not be fixed in a
> future service pack for SQL Server 2000.
> Additionally, for SQL Server 2005 (codename Yukon) will support accent
> sensitive or insensitive fulltext search via new T-SQL:
> CREATE FULLTEXT CATALOG fulltext_catalog_identifier ON FILEGROUP
> filegroup_identifier
> IN PATH <root path> WITH ACCENT SENSITIVE | INSENSITIVE AS DEFAULT
> Regards,
> John
>
> "Emil Mustea" <emil@.sonic.ro> wrote in message
> news:OP22HkgKEHA.3392@.TK2MSFTNGP10.phx.gbl...
> not
> not
> understand
>
|||You're welcome, Emil,
Would you be able to provide the ascii code for these letters from the
Romanian code page?
For example for the english accented letter: SELECT ascii('') -- returns:
224
Thanks,
John
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:OO2gNBwKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> Thank you for your detailed reply.
> In Romanian we have a 2 letters with accents in the bottom of the letter
> ""(means "sh" in english) ""(it's sounds like zz in Pizza) . These
letters[vbcol=seagreen]
> will be considered accented?
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:uLIxb5hKEHA.3216@.tk2msftngp13.phx.gbl...
> no.
> -- -- --
> --
> accented
> HalfPipe
> accented
> HalfPipe
> the
a[vbcol=seagreen]
will[vbcol=seagreen]
will[vbcol=seagreen]
writes
>
|||= 186
= 254
"John Kane" <jt-kane@.comcast.net> wrote in message
news:ez5dgUwKEHA.1392@.TK2MSFTNGP09.phx.gbl...
> You're welcome, Emil,
> Would you be able to provide the ascii code for these letters from the
> Romanian code page?
> For example for the english accented letter: SELECT ascii('') --
returns:[vbcol=seagreen]
> 224
> Thanks,
> John
>
> "Emil Mustea" <emil@.sonic.ro> wrote in message
> news:OO2gNBwKEHA.2024@.TK2MSFTNGP11.phx.gbl...
> letters
is[vbcol=seagreen]
> -- -- --
HalfPipe[vbcol=seagreen]
HalfPipe[vbcol=seagreen]
with[vbcol=seagreen]
for[vbcol=seagreen]
non-accented[vbcol=seagreen]
in
> a
> will
> will
> writes
>
|||Emil,
Yes, I believe that the ascii code values above 127 are "extended character"
and are treated as accented characters.
The best way to confirm this is to put Romanian words that contain these
accented letters in a SQL table defined with one of the following datatypes:
Nvarchar, Nchar, or NText. Then create a FT Index on this column using the
Neutral "Language for Word Breaker" and the confirm that Romanian words that
contain these accented letters, are returned together with similar
non-accented letters using CONTAINS or FREETEXT. "The proof is always in
the pudding" - to paraphrase an old English saying .. <G>
Regards,
John
"Emil Mustea" <emil@.sonic.ro> wrote in message
news:eO7GnM9KEHA.3596@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> = 186
> = 254
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:ez5dgUwKEHA.1392@.TK2MSFTNGP09.phx.gbl...
> returns:
letter[vbcol=seagreen]
SP3a)[vbcol=seagreen]
> is
accent[vbcol=seagreen]
> -- -- --
> HalfPipe
> HalfPipe
> with
> for
> non-accented
> in
accent
>