This appears to be mainly a documentation issue. I had originally thought that this was a bug within the REPLICATE
function, but I've done a bit of testing across most other string functions and have found that this behavior is common among most of them. To summarize, this behavior is:
- If there is one "string" parameter and
VARBINARY
is passed in, it is assumed to be VARCHAR
in the code page associated with the database's default collation.
- If there are multiple "string" parameters:
- If all string params are passed in as
VARBINARY
, they are assumed to be VARCHAR
- If one or more params are passed in as
VARCHAR
and none as NVARCHAR
, the VARBINARY
is assumed to be VARCHAR
- If any string params are passed in as
NVARCHAR
, the VARBINARY
is assumed to be NVARCHAR
As far as I can tell, the only string function that will return VARBINARY
given VARBINARY
input is SUBSTRING
.
So the problem is really that the documentation isn't specific enough. The statement:
string_expression can be either character or binary data.
is not incorrect. It just doesn't state that binary data will be implicitly converted to VARCHAR
. If this were to be stated, then getting VARCHAR
output for VARBINARY
input makes sense, and thus the statement:
Returns the same type as string_expression.
is also not incorrect.
This behavior, of course, would ideally be noted across all of the functions that it pertains to, and not just this one.
I have already added this info to the documentation issue on GitHub that was opened by the O.P..