First let me say that I agree that the documentation is a little light on the details of the parameters that are accepted by each function and the behaviour of each function as it relates to the types of input parameters. I have discussed this with the documentation team in the past, and I spent several days last fall generating a method to semi-automatically compiling a report of all input combinations to all of the functions and the resultant outputs (but the information about the exact translation that occurs being inputs and outputs is not generated). The issue is complex and so far we have not found a simple way to represent the necessary information in the documentation.

The answer to the question "How do string functions work with binary data?" is "It depends on which function is being used". I.e. there is not a one-answer-fits-all-functions answer.

There are basically two classes of functions (hence two answers) though:

• The first class of functions are those that explicitly handle binary data: these include functions like length(), len(), byte_length(), substr(), substring(), byte_substr(), csconvert(), to_char(), to_nchar(), hash(), encrypt(), decrypt(), isencrypted(), compress(), decompress(), base64_encode(), string(), patindex(), ... and maybe some more that I missed? These functions have explicit code that determines if the input is binary or a char type and then does something (that hopefully makes sense to you) based on that information.
• The second class of functions are those that only handle [n][var]char data as input. These functions assume that the input is in database character collation (or database nchar collation) and therefore processes the [n]char inputs according to the corresponding collation rules (e.g. steps through the strings character-by-character where each 'character' is defined by the character set being used).

When binary input is given where a char type input is expected the binary data is automatically casted to char type (using an identity mapping - i.e. no conversion in the data is performed).

For example, providing binary inputs to REPLACE results in the binary data being treated as character data in the database CHAR character set and characters are replaced one-by-one accordingly. As such what you get out will depend entirely on the character set being used (and of course the inputs). E.g.

begin
   declare @bin long binary;
   declare @fr  long binary;
   declare @to  long binary;

  set @bin = hextobin( '0102030405060708090a0b0c0d0e0f' );

  set @fr = hextobin( '01' );
  set @to = hextobin( '40' );

  select bintohex( @bin ), bintohex( replace( @bin, @fr, @to ) )
end;

The output from the above is:

'0102030405060708090A0B0C0D0E0F','4002030405060708090A0B0C0D0E0F'

I would agree that a binary replace (e.g. byte_replace()) function could be useful... although I'm sure a crafty programmer might be able to come up with a solution using the existing capabilities. This is left as an exercise for the reader! ;-)