FIX: Unicode Wildcard Comparisons with Latin1_general_CI_AS May Return Incorrect Results (308772)


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
This article was previously published under Q308772
BUG #: 354663 (SHILOH_BUGS)

SYMPTOMS

For the case-insensitive Latin1_general_CI_AS collation, LIKE comparisons of some special Unicode characters may not always be correct.

CAUSE

There are some Unicode characters whose meaning depends on prior characters in the string. Pattern matching is not properly implemented for wildcard searches of those special Unicode characters for the Latin1_general_CI_AS (English case-insensitive) collation only. This is the default collation for SQL Server.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How to Obtain the Latest SQL Server 2000 Service Pack

WORKAROUND

Use a different collation when querying these types of Unicode characters.

STATUS

Microsoft has confirmed that this is a problem in Microsoft SQL Server 2000. This problem was first corrected in Microsoft SQL Server 2000 Service Pack 2.

MORE INFORMATION

For an example of some of the Unicode characters that can cause this problem, see the following code:

NOTE: The results returned in a case-sensitive and case-insensitive comparison are different. 
Repro:
set nocount on
use tempdb
go
create table Unicode_Characters  (c nvarchar(20))
go
insert into Unicode_Characters values (convert(nvarchar, 0xfa017500))
insert into Unicode_Characters values (convert(nvarchar, 0xc50001037500)) 
insert into Unicode_Characters values (convert(nvarchar, 0x41000a037500)) 
insert into Unicode_Characters values (convert(nvarchar, 0x610001037500)) 
insert into Unicode_Characters values (convert(nvarchar, 0x61000a0301037500))
insert into Unicode_Characters values (convert(nvarchar, 0xfb017500))
insert into Unicode_Characters values (convert(nvarchar, 0xe50001037500)) 
insert into Unicode_Characters values (convert(nvarchar, 0x61000a037500)) 
insert into Unicode_Characters values (convert(nvarchar, 0x610001037500)) 
insert into Unicode_Characters values (convert(nvarchar, 0x61000a0301037500)) 

select c from Unicode_Characters
  where c like '_u' collate Latin1_general_CS_AS
select c from Unicode_Characters
  where c like '_u' collate Latin1_general_CI_AS
Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbbug kbfix KB308772
©2003 Microsoft Corporation. All rights reserved.