Incomplete / wrong query results
Todor Todorov
=================
SQL Server 2000 SP3a
Danish_Norwegian_CI_AS collation
Comp Level 80
Example SQL:
=========
create table tst (col varchar(100))
insert into tst values('NESAAS')
select * from tst where col like 'NESA%'
I would expect the query to return 1 row, but it returns 0 rows. This happens when using Danish Norwegian dictionary collation. Changing to binary collation returns the correct result but has undesired side effects. In other words, it's a production system where it's not possible to change the collation.
Is this a bug in MS SQL server and what can I do to make it perform correct queries?
Jacco Schalkwijk
select * from tst where col COLLATE Latin1_General_BIN like 'NESA%'
--
Jacco Schalkwijk
SQL Server MVP
"Todor Todorov" <h...@cdm.dk> wrote in message
news:4CDE30B3-A70E-4AD6...@microsoft.com...
Baisong Wei[MSFT]
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
Before I carry out the explanation, could you run the following could in
the query Analyzer?
use pubs
go
create table tst2
(col varchar(100) collate Danish_Norwegian_CI_AS)
insert into tst2 values('NESAAS')
select * from tst2 where col like '%A%'collate Danish_Norwegian_CI_AS
select * from tst2 where col like 'NESA%'collate Danish_Norwegian_CI_AS_KS
select * from tst2 where col like 'NES%'collate Danish_Norwegian_CI_AS
select * from tst2 where col like 'NE%'collate Danish_Norwegian_CI_AS
select * from tst2 where col like 'N%'collate Danish_Norwegian_CI_AS
drop table tst2
You will get
--no record selected
--no record selected
NESAAS
NESAAS
NESAAS
Then ,run the following code
create table tst2
(col varchar(100) collate Danish_Norwegian_CI_AS)
insert into tst2 values('NESAAAS')
select * from tst2 where col like '%A%'collate Danish_Norwegian_CI_AS
select * from tst2 where col like 'NESA%'collate Danish_Norwegian_CI_AS_KS
select * from tst2 where col like 'NES%'collate Danish_Norwegian_CI_AS
select * from tst2 where col like 'NE%'collate Danish_Norwegian_CI_AS
select * from tst2 where col like 'N%'collate Danish_Norwegian_CI_AS
drop table tst2
You will get
NESAAAS
--no record selected
NESAAAS
NESAAAS
NESAAAS
It is because the difference between the Danish alphabet and English. AA is
sometimes considered as a equivalent of an alphbet like A ( sorry I cannot
type it out, let me take 'X' instead. So, when there is AA and you use LIKE
'A%', system could take AA as X but not AA, However, if there is AAA, like
'NESAAAS', it will take AA as X and the left A will fit the creteria of the
LIKE clause. It is not a bug but some collation difference. So when run a
query, just take it into consideration, such as for a (lower case)
For more information, you could look at this article:
http://www.bbc.co.uk/dna/h2g2/A292682
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
Baisong Wei[MSFT]
I am reviewing your post and wonder that if you still have the question
that related to the Danish collation problems. I suggest that avoid using
the singel 'A%' for LIKE query for some special alphabet in Danish. I hope
it is helpful to your question. If you still have question about it, please
feel free to post your message here and we are glad to help.
Thanks
Todor Todorov
Sorry for the delay of my reply. I've not been at work last week and first
now had a chance to see your posting. Yes, I still have a problem, or should
I say our users/customers have a problem. Each time they want to perform a
search and only entering one A, words containing double A are left out of
the result.
You are correct, that AA is the same as Å (A ring). This is because the
letter Å (A ring) was introduced recently to the Danish alphabet and older
texts and especially names still use the old way - writing double AA.
However, not every double AA should be treated as Å (A ring). Example could
be the company name BAAN.
Most of our users are Danes and most of them choose the default
Danish_Norwegian_CI_AS collation. However it is very difficult to explain
end users why they can't search for example for 'BAAN' by entering 'BA'. My
question is what collation should be used to tell SQL Server not to treat AA
as Å (A ring) but still sort and handle the rest of the Danish letters
correctly? Is it possible to make your own collation? Is there a KB article
describing that issue?
Thanks
-- Todor
"Baisong Wei[MSFT]" <v-ba...@online.microsoft.com> wrote in message
news:CKQ7qeKB...@cpmsftngxa06.phx.gbl...
Vikrant V Dalwale [MSFT]
Hello Todor,
There is no Fix for this issue since this is a behavior by Design.
In Danish aa is expected to be treated as equivalent to å, and the
only reason to select the Danish_Norwegian_CI_AS collation is if you
want to have string sorts and comparisons use the culturally correct
rules for those languages. If you don’t want to use Danish rules for
string comparisons, then the solution would be to use a different
collation that doesn’t include these special cases. You might need
to test with
different collations and select the one which gives you the desired
results.
Does that answer your question ?
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer’s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
>From: "Todor Todorov" <h...@cdm.dk>
>References: <4CDE30B3-A70E-4AD6...@microsoft.com>
<CKQ7qeKB...@cpmsftngxa06.phx.gbl>
>Subject: Re: Incomplete / wrong query results
>Date: Sun, 21 Mar 2004 23:36:35 +0100
>Lines: 51
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.0
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Message-ID: <#y#g8T5DE...@TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.setup
>NNTP-Posting-Host: skansen-50-149.ip-pluggen.com 212.181.50.149
>Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.setup:63023
>X-Tomcat-NG: microsoft.public.sqlserver.setup
Todor Todorov
Thanks for the answer. Not the answer I've hoped for, but I see there is
nothing to be done about the collation issue. One last thing, is this
problem or feature about searching for AA mentioned somewhere in the SQL
server documentation or any knowledgebase articles?
-- Todor
"Vikrant V Dalwale [MSFT]" <vikr...@online.microsoft.com> wrote in message
news:0N78NzEE...@cpmsftngxa06.phx.gbl...
Vikrant V Dalwale [MSFT]
Hello Todor,
Since this issue is a result of introduction of new Danish Alphabets
(and its representation) and has nothing to do with SQL Server
Behavior,
as per my knowledge, it is not documented in SQL Server Docs or
Knowledgebase.
Thanks for using MSDN Newsgroup.
Vikrant Dalwale
Microsoft SQL Server Support Professional
Microsoft highly recommends to all of our customers that they visit
the http://www.microsoft.com/protect site and perform the three
straightforward steps listed to improve your computer’s security.
This posting is provided "AS IS" with no warranties, and confers no
rights.
--------------------
>From: "Todor Todorov" <h...@cdm.dk>
>References: <4CDE30B3-A70E-4AD6...@microsoft.com>
<CKQ7qeKB...@cpmsftngxa06.phx.gbl>
<#y#g8T5DE...@TK2MSFTNGP11.phx.gbl>
<0N78NzEE...@cpmsftngxa06.phx.gbl>
>Subject: Re: Incomplete / wrong query results
>Date: Thu, 25 Mar 2004 11:28:18 +0100
>Lines: 133
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.3790.0
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
>Message-ID: <OAVVoPlE...@TK2MSFTNGP11.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.setup
>NNTP-Posting-Host: skansen-50-149.ip-pluggen.com 212.181.50.149
>Path:
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTNGXA05.phx.gbl!TK2MSF
TNGP08.phx.gbl!TK2MSFTNGP11.phx.gbl
>Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.setup:63135
>X-Tomcat-NG: microsoft.public.sqlserver.setup