sql - Return binary as string, NOT convert or cast

Question

Is there a simple way (like convert or cast) to return a binary field as a string without actually converting it or casting it?

The data I want looks like this in the database:

0x24FC40460F58D64394A06684E9749F30

When using convert(varchar(max), binary_field), it comes back like this:

$ü@FX?C”?f?ét?0

cast(binary_field as varchar(max)) had the same results.

I'm trying to modify a datasource in an old vb.net application, so I want it to be a string like the existing values, but still look like the original data. I don't have to do it in the query, but I'd like to if possible to avoid modifying the old vb code too much.

Here is my select statement:

SELECT strName, strDesc, binUUID
  FROM MyTableName
  where intFamily = '1234'
  order by strName

Alternatively, if I do cast/convert it, would it be safe to compare "$ü@FX?C”?f?ét?0" stored as a string at a later time to another value gathered in the same way?

Answer 1

There is a built in function to generate hex strings from binary values

SELECT sys.fn_varbintohexstr (0x24FC40460F58D64394A06684E9749F30)