BUG: Some String Functions Do Not Work as Expected on Binary Data (276195)

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