Microsoft KB Archive/308772

= FIX: Unicode Wildcard Comparisons with Latin1_general_CI_AS May Return Incorrect Results =

Article ID: 308772

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



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

Additional query words: wrong incorrect data results

Keywords: kbbug kbfix KB308772

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.