SQLTeam.com | Weblogs | Forums

Decoding varbinary column values

Transact-SQL
#1

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

#2

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.

#3

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.

#4

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

#5

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.