SQLTeam.com | Weblogs | Forums

Decoding varbinary column values

We are trying to convert varbinary column values to a readable text using convert and cast but it seems to return same hex values or unreadable characters. Any help is appreciated.

Here is the query using CONVERT/CAST and also tried converting to VARCHAR(max).
SELECT id, elementarray, CONVERT(NVARCHAR(max),elementarray, 0) c0
,CONVERT(NVARCHAR(max), elementarray, 1) c1
,CONVERT(NVARCHAR(max), elementarray, 2) c2
,CAST(elementarray as NVARCHAR(max)) c
FROM dbo.Table
WHERE elementarray is not NULL;

Here is a sample varbinary value from elementarray column:
0x0A02FAC1A67AC20080BBC20A0270C1A2F8C1A238C20A0280C1B1C2A240C20A02C8BEA248BFA296BF220280BFA280BF3602FA42A2C83F

The 0x00 char in the input is causing the result to truncate at that byte. The hex values in most cases don't represent standard ASCII chars (A-Z, 0, 1, 2, etc.) anyway, so SQL can't convert them to standard chars.

Thank you, @ScottPletcher. The database that this table resides in is managed by a vendor and we do not know whether the source data of the varbinary column is a text, image or something else.

Ok, makes sense. But if it's an image, it doesn't make sense to try to CONVERT it to chars.

Just heard back from vendor and confirmed the data in the varbinary columns are encrypted which explains why they cannot be converted using CAST/CONVERT.