incorrect query reults

Hi,
another problem occured!-)
I did the following SQL-query:
select * from queue_suchsourceindex
where contains(text, ' "oenorm*" ')
But in the result there are many rows, that do not contain the word "oenorm"
in its text field. First i thought i mixed somethiong up in Application, but
everything is working right there. Just the query return the wrong results.
I have no clue, how to find out what the problem is.
thx in advance, Gerald.
my configuration:
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
I forgot to tell, that the query
select id, url from queue_suchsourceindex
where text like '%oenorm%'
returns no rows, as it should.
thx, Gerald.
"Gerald Baeck" schrieb:

> Hi,
> another problem occured!-)
> I did the following SQL-query:
>
> select * from queue_suchsourceindex
> where contains(text, ' "oenorm*" ')
> But in the result there are many rows, that do not contain the word "oenorm"
> in its text field. First i thought i mixed somethiong up in Application, but
> everything is working right there. Just the query return the wrong results.
> I have no clue, how to find out what the problem is.
> thx in advance, Gerald.
> my configuration:
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
|||Gerald,
Why would you expect contains(text, ' "oenorm*" ') to return results when
using text like '%oenorm%' does not return results?
While often the T-SQL LIKE will return different results when compared to
CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
matching" method and using leading and trailing wildcards (%), then most
likely CONTAINS or FREETEXT will not return results as well as the search
string is most likely not in your column: text.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...[vbcol=seagreen]
> I forgot to tell, that the query
> select id, url from queue_suchsourceindex
> where text like '%oenorm%'
> returns no rows, as it should.
> thx, Gerald.
>
> "Gerald Baeck" schrieb:
"oenorm"[vbcol=seagreen]
but[vbcol=seagreen]
results.[vbcol=seagreen]
Windows[vbcol=seagreen]
|||John,
its the other way round. The CONTAINS-Query returns many rows and the
LIKE-Query does not. The LIKE-QUery return the correct resultset in
that case.
thx, Gerald.
"John Kane" <jt-kane@.comcast.net> wrote in message news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
> Gerald,
> Why would you expect contains(text, ' "oenorm*" ') to return results when
> using text like '%oenorm%' does not return results?
> While often the T-SQL LIKE will return different results when compared to
> CONTAINS or FREETEXT, but if T-SQL LIKE cannot find it with it's "pattern
> matching" method and using leading and trailing wildcards (%), then most
> likely CONTAINS or FREETEXT will not return results as well as the search
> string is most likely not in your column: text.
> Regards,
> John
>
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:418794BC-8ED0-4B72-9CC7-0AF3C50C04F5@.microsoft.com...
> "oenorm"
> but
> results.
> Windows
|||Thanks, Gerald,
Could you proved the exact text from a few of the rows that contain the
string 'oenorm', including all punctuation characters?
Is this HTML code or plain text that is stored in a text or varchar column?
Also, what is the language of the text stored in the column?
All of these factors, when considered with the OS platform (in your case
Win2K) can affect the results from the CONTAINS query.
Regards,
John
"Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> John,
> its the other way round. The CONTAINS-Query returns many rows and the
> LIKE-Query does not. The LIKE-QUery return the correct resultset in
> that case.
> thx, Gerald.
>
> "John Kane" <jt-kane@.comcast.net> wrote in message
news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...[vbcol=seagreen]
when[vbcol=seagreen]
to[vbcol=seagreen]
"pattern[vbcol=seagreen]
search[vbcol=seagreen]
Application,[vbcol=seagreen]
14:22:05[vbcol=seagreen]
|||Hi John,
First of all thanks for your patient help!-)
I checked all the rows manually and i am sure that there is no string like
'oenorm'. You can have a look at this File
http://www.webwatch.at/oenorm_example.txt.
The data is stored in plain text and contains no html-tags.
The language of the text-field is german.
regards, Gerald.
"John Kane" wrote:

> Thanks, Gerald,
> Could you proved the exact text from a few of the rows that contain the
> string 'oenorm', including all punctuation characters?
> Is this HTML code or plain text that is stored in a text or varchar column?
> Also, what is the language of the text stored in the column?
> All of these factors, when considered with the OS platform (in your case
> Win2K) can affect the results from the CONTAINS query.
> Regards,
> John
>
> "Gerald Baeck" <exc3ktest@.hotmail.com> wrote in message
> news:ac510211.0411152308.3ec64f25@.posting.google.c om...
> news:<OtWs$f1yEHA.3656@.TK2MSFTNGP09.phx.gbl>...
> when
> to
> "pattern
> search
> Application,
> 14:22:05
>
>
|||You're welcome, Gerald,
I too searched the oenorm_example.txt file and found no hit on oenorm. While
asking for the @.@.version info is a start, sometimes for these more difficult
cases, I've most often ask for additional info as the different results may
be because of the language and possibly not using the correct "Language for
Word Breaker". Could you post the output from the following SQL code?
use <your_database_name_here>
go
SELECT @.@.language
-- may need to set show advanced options
sp_configure 'default full-text language'
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
Depending upon the "Language for Word Breaker" that you are using along with
German text, this may explain the differences in results.
Thanks,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...[vbcol=seagreen]
> Hi John,
> First of all thanks for your patient help!-)
> I checked all the rows manually and i am sure that there is no string like
> 'oenorm'. You can have a look at this File
> http://www.webwatch.at/oenorm_example.txt.
> The data is stored in plain text and contains no html-tags.
> The language of the text-field is german.
> regards, Gerald.
> "John Kane" wrote:
column?[vbcol=seagreen]
compared[vbcol=seagreen]
most[vbcol=seagreen]
message[vbcol=seagreen]
word[vbcol=seagreen]
wrong[vbcol=seagreen]
Edition on[vbcol=seagreen]
|||Hi John,
here is the info:
Deutsch
default full-text language0214748364710311031
5Queue_SuchsourceC:\Programme\Microsoft SQL Server\MSSQL\FTDATA01
dboQueue_SuchsourceIndexPK_Queue_SuchsourceIndex41Queue_Suchsource
dbo1129771082Queue_SuchsourceIndexText2NULLNULL1031
Queue_SuchsourceIndexdbouser table2004-11-15 01:22:52.980
Urlvarcharno255 nononoSQL_Latin1_General_CP1_CI_AS
Texttextno16 yes(n/a)(n/a)German_PhoneBook_CI_AS
Zeitsmalldatetimeno4 no(n/a)(n/a)NULL
IDintno410 0 no(n/a)(n/a)NULL
ID110
No rowguidcol column defined.
PRIMARY
PK_Queue_SuchsourceIndexclustered, unique, primary key located on PRIMARYID
PRIMARY KEY (clustered)PK_Queue_SuchsourceIndex(n/a)(n/a)(n/a)(n/a)ID
WebinvestigatorDB.dbo.Queue_Clippings: FK_Queue_Treffer_Queue_SuchsourceIndex
thx, Gerald.
"John Kane" wrote:

> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> 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
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||I noticed something interesting. I get now hits with the term "oenorm", but
the couple of incorrect hits with "oenorm*".
regards, Gerald.
"John Kane" wrote:

> You're welcome, Gerald,
> I too searched the oenorm_example.txt file and found no hit on oenorm. While
> asking for the @.@.version info is a start, sometimes for these more difficult
> cases, I've most often ask for additional info as the different results may
> be because of the language and possibly not using the correct "Language for
> Word Breaker". Could you post the output from the following SQL code?
> use <your_database_name_here>
> go
> SELECT @.@.language
> -- may need to set show advanced options
> sp_configure 'default full-text language'
> 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
> Depending upon the "Language for Word Breaker" that you are using along with
> German text, this may explain the differences in results.
> Thanks,
> John
>
>
> "Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
> news:98593AF6-6E55-4B6C-BEAC-5863743453A9@.microsoft.com...
> column?
> compared
> most
> message
> word
> wrong
> Edition on
>
>
|||Thanks, Gerald,
You're using German (windows collation 1031) for all aspects of your server,
including the @.@.language, default FTS language as well as the "Language for
Word Breaker" for your FT-enabled table (Queue_SuchsourceIndex) Text (text)
column. Additionally, you're using SQL Server 2000 SP3 on NT 5.0 (or Win2K),
so you're using the German infosoft.dll wordbreaker.
Now, the issue is understanding how the Win2K German infosoft.dll
wordbreaker breaks or tokenizes the German *word* oenorm. Getting hits with
"oenorm", but the couple of incorrect hits with "oenorm*", might be at the
heart of this issue with the Win2K German infosoft.dll wordbreaker, as I
neither read or write German, can you tell me what this word means in
English? Is it normally a stand-alone German word or is it a part of a
compound word in German? If so, is it usually used as a the leading part or
the trailing part of the compound word?
FYI, the Win2K German infosoft.dll wordbreaker has been problematic at best
and at worst not very useful and for this and other reasons, MS has
developed a new word breaker (langwbrk.dll) for the WinXP and Win2003 OS
platforms. Although, I'm not sure if for the German language, if this
wordbreaker is any better, I'm sad to say.
Regards,
John
"Gerald Baeck" <GeraldBaeck@.discussions.microsoft.com> wrote in message
news:5703BB7B-F1FD-43BE-BF4D-BA777577214F@.microsoft.com...
> I noticed something interesting. I get now hits with the term "oenorm",
but[vbcol=seagreen]
> the couple of incorrect hits with "oenorm*".
> regards, Gerald.
> "John Kane" wrote:
While[vbcol=seagreen]
difficult[vbcol=seagreen]
may[vbcol=seagreen]
for[vbcol=seagreen]
with[vbcol=seagreen]
like[vbcol=seagreen]
the[vbcol=seagreen]
case[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
results[vbcol=seagreen]
then[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
2002[vbcol=seagreen]