1

I have a SQL Server table with a VARBINARY column, which contains values like these:

0x500073007900630068006F006C006F0067006900630061006C00200053007400720061007400...

These values represent HTML files.

I'm trying to figure out how to SELECT these values as HTML strings.

I tried selecting CONVERT(VARCHAR(max), Content, 2) AS ContentConverted – but that just gave me the same value with the 0x prefix removed:

500073007900630068006F006C006F0067006900630061006C00200053007400720061007400...

I also tried CAST(Content AS VARCHAR(max)) AS ContentConverted – but that (bizarrely) just gives me a single letter:

P

Lastly, I tried this hack:

CAST(CONCAT('<?xml version="1.0" encoding="UTF-8" ?><![CDATA[',Content,']]>') AS XML).value('.','nvarchar(max)')

...and that just produces an "illegal xml character" error.

Any ideas?

Travis Miller
  • 11
  • 2
  • 1
    _but that (bizarrely) just gives me a single letter_ Why is that bizarre? As ascii, the second tuple 0x00 is the string terminator. The first tuple is 0x50 which is the letter P. So it seems you have unicode characters and you should be able to "see" the string by casting to [unicode](https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=09906f06b5163aff4c3ff652dc29eab3). – SMor Apr 08 '22 at 13:17
  • @SMor: casting to NVARCHAR did the trick! Thanks. I'm new to SQL Server, so I didn't understand the difference between VARCHAR and NVARCHAR. If you want to post this as an answer, I'll flag it as the best answer. – Travis Miller Apr 08 '22 at 13:27
  • @TravisMiller, `nvarchar` is Unicode. The binary value in your question is 2-bytes per character (UTF-2 Unicide encoding). – Dan Guzman Apr 08 '22 at 14:26
  • Does this answer your question? [varbinary to string on SQL Server](https://stackoverflow.com/questions/3289988/varbinary-to-string-on-sql-server) – jasie Apr 11 '22 at 12:00

0 Answers0