Undocumented conversion of binary variable to a string

There is an undocumented function in SQL Server for performing the conversion of a binary value into a string representation of the binary value i.e. 0x0233DAF to '0x0233DAF'. You can't just convert to a varchar because the varchar is just the characters representing the ascii values in the binary i.e

select cast(0x73696D6F6E0000000000 as varchar(20))

results in 'simon', rather than '0x73696D6F6E0000000000'

The function in question is dbo.fn_sqlvarbasetostr.

What is odd is that the function is still undocumented in SQL Server 2005 although its still present. This may suggest that it might not exist in the future so be warned.

Published Saturday, November 12, 2005 6:06 PM by simonsabin
Filed under:


No Comments