FIX: Patindex Function May Return Incorrect Result on SQL Server 7.0 with DBCS Codepage (278965)

The information in this article applies to:

Microsoft SQL Server 7.0

This article was previously published under Q278965

BUG #: 57922 (SQLBUG_70)

SYMPTOMS

If SQL Server 7.0 is installed with a DBCS codepage (for instance, Japanese-932, Korea-949, or Chinese-936/950) and a dictionary sort order (both case-sensitive and case-insensitive), the Patindex function may return incorrect results. For instance, the following query:

select patindex('%test%', convert(text, 'abctestabc'))

returns 3 instead of the expected result of 4.

WORKAROUND

To work around this problem, avoid converting a string to a text data type. The following code returns the expected result of 4:

select patindex('%test%', 'abctestabc')

-or-

select patindex('%test%', convert(varchar(8000), 'abctestabc'))

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.

Modification Type:	Major	Last Reviewed:	3/14/2006
Keywords:	kbBug kbfix KB278965