Microsoft KB Archive/276195

= BUG: Some String Functions Do Not Work as Expected on Binary Data =

Article ID: 276195

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q276195



BUG #: 57673 (SQLBUG_70)

BUG #: 235765 (SHILOH)



SYMPTOMS
Some string functions do not behave as expected with binary data.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.

Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
The functions that do not behave as expected with binary data include:
 * STUFF
 * REPLACE
 * REVERSE
 * LEFT
 * RIGHT
 * LEN
 * PATINDEX

Other functions work correctly but return varchar rather than varbinary. These functions include:
 * LTRIM
 * RTRIM
 * REPLICATE

The SUBSTRING and CHARINDEX functions work correctly. The CAST function produces the same results as the CONVERT function in the following examples.

Run the following statement in the SQL Query Analyzer: select convert(varbinary(6),stuff(convert(binary(6),0x00001111),3,1,0x81)) The correct result with Code Page 1252 is 0x000081110000. Code page 1252 (ISO character set) is the default character set. It is also known as the ISO 8859-1, Latin 1, or ANSI character set. The result with Code Page 936 is 0x0000110000. The result with Code Page 936 is not correct.

Run the following statement in the SQL Query Analyzer: select convert(varbinary(6),stuff(convert(binary(6),0x00001111),3,1,0x80)) The correct result with both Code Page 936 and 1252 is 0x000080110000. The results are correct for both code pages through Hex 80 or 128 decimal.

The following query illustrates the incorrect results when code page 936 is used with the STUFF function. set nocount on select cast(stuff(cast(0x00001111 as binary(4)),3,1,0x80)  as binary(4)) as [H80] , cast(stuff(cast(0x00001111 as binary(4)),3,1,0x81)  as binary(4)) as [H81] , cast(stuff(cast(0x00001111 as binary(4)),3,1,0x99)  as binary(4)) as [H99] , cast(stuff(cast(0x00001111 as binary(4)),3,2,0x8111) as binary(4)) as [H8111] , cast(stuff(cast(0x00001111 as binary(4)),3,2,0x9911) as binary(4)) as [H9911] , cast(stuff(cast(0x00001111 as binary(4)),3,2,0x0100) as binary(4)) as [H0100] , cast(stuff(cast(0x00001111 as binary(4)),3,2,0x0101) as binary(4)) as [H0101] The following incorrect result is from code page 936: H80       H81        H99        H8111      H9911      H0100      H0101 -- -- -- -- -- -- -- 0x00008011 0x00001100 0x00001100 0x00008111 0x00009911 0x00000100 0x00000101 The following correct result is from code page 1252. H80       H81        H99        H8111      H9911      H0100      H0101 -- -- -- -- -- -- -- 0x00008011 0x00008111 0x00009911 0x00008111 0x00009911 0x00000100 0x00000101 The following query illustrates this issue with the REPLACE function. set nocount on select cast(replace(cast(0x00001111 as binary(4)),0x11,0x80)    as binary(4)) as [H80] , cast(replace(cast(0x00001111 as binary(4)),0x11,0x81)    as binary(4)) as [H81] , cast(replace(cast(0x00001111 as binary(4)),0x11,0x99)    as binary(4)) as [H99] , cast(replace(cast(0x00001111 as binary(4)),0x1111,0x8111) as binary(4)) as [H8111] , cast(replace(cast(0x00001111 as binary(4)),0x1111,0x9911) as binary(4)) as [H9911] , cast(replace(cast(0x00001111 as binary(4)),0x1111,0x0100) as binary(4)) as [H0100] , cast(replace(cast(0x00001111 as binary(4)),0x1111,0x0101) as binary(4)) as [H0101] The following incorrect result is from code page 936: H80       H81        H99        H8111      H9911      H0100      H0101 -- -- -- -- -- -- -- 0x80800000 0x00000000 0x00000000 0x81110000 0x99110000 0x01000000 0x01010000 The following correct result is from code page 1252. H80       H81        H99        H8111      H9911      H0100      H0101 -- -- -- -- -- -- -- 0x00008080 0x00008181 0x00009999 0x00008111 0x00009911 0x00000100 0x00000101 The following query illustrates this issue with the REVERSE function. set nocount on select cast(reverse(cast(0x00111180 as binary(4)))  as binary(4)) as [binary4_80] , cast(reverse(cast(0x00111181 as binary(4))) as binary(4)) as [binary4_81] The following incorrect result is from code page 936: binary4_80 binary4_81 -- -- 0x80111100 0x11110000 The following correct result is from code page 1252. binary4_80 binary4_81 -- -- 0x80111100 0x81111100 The following query illustrates this issue with the REVERSE function. set nocount on select cast(left(cast(0x00111180 as binary(4)),4)  as binary(4)) as [binary4_80] , cast(left(cast(0x00111181 as binary(4)),4) as binary(4)) as [binary4_81] The following incorrect result is from code page 936: binary4_80 binary4_81 -- -- 0x00111180 0x00111100 The following correct result is from code page 1252: binary4_80 binary4_81 -- -- 0x00111180 0x00111181

Keywords: kbbug kbcodesnippet kbpending KB276195

-

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

© Microsoft Corporation. All rights reserved.