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.
2012年3月26日星期一
full text search on doc files
I have a list of word files (.doc) I am trying to us the full text
search in sql server to find key words in these word documents. I have
a Windows XP and sql server installed on my machine. ( no network
server). I cant seem to get the code to work to do a search on these
files. I would really appreciate if someone can send me some code from
start to finsh so I can create the sql tables and then catolog to
search for keywords in the word files.What version of SQL Server are you using? Word documents must be stored in
columns of data type image, and another column must be present to store the
type suffix (".doc" for MS Word files).
When full-text indexing is activated for an image column the name of the
column where the suffix is stored must also be set. How this is actually don
e
depends on the SQL Server version.
SQL 2000:
sp_fulltext_column
@.tabname = '<qualified_table_name>'
,@.colname = '<column_name>'
,@.action = 'add'
,@.type_colname = '<type_column_name>'
SQL 2005:
create fulltext index on <table_name>
(<column_name> TYPE COLUMN <type_column_name>
key index <index_name>
If the index has been created appropriately, then you might also check the
SQL Error Log:
http://milambda.blogspot.com/2005/1...n-or-bybug.html
ML
http://milambda.blogspot.com/|||ML,
How do i create the table first and get the files indexed? How do I
get the files catologued? i am using sql2000 . Do i run the sql you
wrote above after the files have been imported?|||Setting up full-text indexing in SQL 2000 is very well documented in Books
Online:
http://msdn.microsoft.com/library/d... />
d_6g1f.asp
After you've created the table and enabled the database for full-text
search, you need to enable the table, then the column for full-text indexing
.
It really doesn't matter whether you set up FTI before inserting data in the
tables, or after, as long as you propagate change to the catalogue before yo
u
try to use full-text search (new rows will not be found by the engine until
the contents have been indexed).
There are several methods of propagating change to the full-text catalogue:
full propagation, incremental propagation and background propagation (with
change tracking). With background propagation new rows are indexed after
they've been inserted and changed rows are indexed after they've been
updated, but there's a performance impact and writetext/updatetext operation
s
are not detected.
To avoid the downside you could use incremental propagation by creating a
SQL Agent Job that starts incremental propagation as frequently as you need.
Are you having any specific problems? You could also ask these questions in
the dedicated newsgorup: "microsoft.public.sqlserver.fulltext".
ML
http://milambda.blogspot.com/|||ML,
I have looked through the link, still not getting it to work. I guess
i need some code, from start to finish, loading the file from the
c:\... and then running the sql. Anything you may have would be
extremely helpful..thanks|||The link I sent you will help you set up full-text indexing, but the actual
method of inserting MS Word documents into the table depends on your client
application.
If you need to insert blobs through T-SQL, you could use BULK INSERT:
http://msdn.microsoft.com/library/d...br />
4fec.asp
Also make sure you create the format file in accordance to this KB article:
http://support.microsoft.com/defaul...kb;en-us;271344
Information on using format files:
http://msdn.microsoft.com/library/d...>
bcp_9yat.asp
ML
http://milambda.blogspot.com/
search in sql server to find key words in these word documents. I have
a Windows XP and sql server installed on my machine. ( no network
server). I cant seem to get the code to work to do a search on these
files. I would really appreciate if someone can send me some code from
start to finsh so I can create the sql tables and then catolog to
search for keywords in the word files.What version of SQL Server are you using? Word documents must be stored in
columns of data type image, and another column must be present to store the
type suffix (".doc" for MS Word files).
When full-text indexing is activated for an image column the name of the
column where the suffix is stored must also be set. How this is actually don
e
depends on the SQL Server version.
SQL 2000:
sp_fulltext_column
@.tabname = '<qualified_table_name>'
,@.colname = '<column_name>'
,@.action = 'add'
,@.type_colname = '<type_column_name>'
SQL 2005:
create fulltext index on <table_name>
(<column_name> TYPE COLUMN <type_column_name>
key index <index_name>
If the index has been created appropriately, then you might also check the
SQL Error Log:
http://milambda.blogspot.com/2005/1...n-or-bybug.html
ML
http://milambda.blogspot.com/|||ML,
How do i create the table first and get the files indexed? How do I
get the files catologued? i am using sql2000 . Do i run the sql you
wrote above after the files have been imported?|||Setting up full-text indexing in SQL 2000 is very well documented in Books
Online:
http://msdn.microsoft.com/library/d... />
d_6g1f.asp
After you've created the table and enabled the database for full-text
search, you need to enable the table, then the column for full-text indexing
.
It really doesn't matter whether you set up FTI before inserting data in the
tables, or after, as long as you propagate change to the catalogue before yo
u
try to use full-text search (new rows will not be found by the engine until
the contents have been indexed).
There are several methods of propagating change to the full-text catalogue:
full propagation, incremental propagation and background propagation (with
change tracking). With background propagation new rows are indexed after
they've been inserted and changed rows are indexed after they've been
updated, but there's a performance impact and writetext/updatetext operation
s
are not detected.
To avoid the downside you could use incremental propagation by creating a
SQL Agent Job that starts incremental propagation as frequently as you need.
Are you having any specific problems? You could also ask these questions in
the dedicated newsgorup: "microsoft.public.sqlserver.fulltext".
ML
http://milambda.blogspot.com/|||ML,
I have looked through the link, still not getting it to work. I guess
i need some code, from start to finish, loading the file from the
c:\... and then running the sql. Anything you may have would be
extremely helpful..thanks|||The link I sent you will help you set up full-text indexing, but the actual
method of inserting MS Word documents into the table depends on your client
application.
If you need to insert blobs through T-SQL, you could use BULK INSERT:
http://msdn.microsoft.com/library/d...br />
4fec.asp
Also make sure you create the format file in accordance to this KB article:
http://support.microsoft.com/defaul...kb;en-us;271344
Information on using format files:
http://msdn.microsoft.com/library/d...>
bcp_9yat.asp
ML
http://milambda.blogspot.com/
2012年3月19日星期一
Full text indexing question
Hi,
I am using full text indexing to index an image column where I am storing HTML documents. Currently I have an extra column in my table storing the file extension (htm) so the indexer knows which filter to use for every row.
HTML is the only format I'll be indexing, so is there a way for me to set this somewhere else so I don't have to have a column full of htm values - this seems really wasteful, those htm's add up when there are millions of rows...
Thanks!
RobWhy don't you use two separate tables, one for you image data, and one for your HTML pages. You may consider in the last case to store the HTML pages as text or ntext.|||I don't have any "image data" - just html files stored in an image datatype column. My specific data structure isn't really relevent - is there an alternative to the file extension column for full text indexing (because it will always contain the same value)?
Hope this makes it clearer
Rob
I am using full text indexing to index an image column where I am storing HTML documents. Currently I have an extra column in my table storing the file extension (htm) so the indexer knows which filter to use for every row.
HTML is the only format I'll be indexing, so is there a way for me to set this somewhere else so I don't have to have a column full of htm values - this seems really wasteful, those htm's add up when there are millions of rows...
Thanks!
RobWhy don't you use two separate tables, one for you image data, and one for your HTML pages. You may consider in the last case to store the HTML pages as text or ntext.|||I don't have any "image data" - just html files stored in an image datatype column. My specific data structure isn't really relevent - is there an alternative to the file extension column for full text indexing (because it will always contain the same value)?
Hope this makes it clearer
Rob
2012年3月11日星期日
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.
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.
2012年2月19日星期日
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
>
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
>
订阅：
博文 (Atom)