SQL Server : hex to base64

Question

I have a SQL Server database with jpeg images stored as hex (0xFFD8...) Is there a way to do a query where result will be in base64 instead of hex?

I tried to google but I can't find anything like it :/

Probably a duplicate of http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql. The accepted answer should meet your requirement. (I can't vote to close as a duplicate because of the open bounty) — Ed Harper, Jan 23 '17 at 09:14
@EdHarper Not really, thats a string to base64, the question was hex to base64. — dadde, Jan 25 '17 at 10:55
@dadde - minimal changes are required to make the answer I linked to work on a binary data type. I stand by the assertion that this is a duplicate. — Ed Harper, Jan 25 '17 at 11:19
@EdHarper okay, I was also looking at that answer but I think the main problem here is the hex conversion, not the base64 conversion. I respect your comment though. — dadde, Jan 25 '17 at 11:21

score 7 · Answer 1 · answered Jan 30 '17 at 00:12

You can convert hex to to varbinary by leveraging the sql parser itself:

DECLARE @TestBinHex varchar(max), @TestBinary varbinary(max), @Statement nvarchar(max);
SELECT @TestBinHex = '0x012345';
SELECT @Statement = N'SELECT @binaryResult = ' + @TestBinHex;
EXECUTE sp_executesql @Statement, N'@binaryResult varbinary(max) OUTPUT', @binaryResult=@TestBinary OUTPUT;
SELECT @TestBinary

This will get sp_executesql to execute dynamic SQL containing the literal 0x012345 which the T-SQL parser understands perfectly well. You can then feed the results of that into the XML trick referred to by @EdHarper as shown below:

DECLARE @TestBinHex varchar(max), @TestBinary varbinary(max), @Statement nvarchar(max);
SELECT @TestBinHex = '0x012345';
SELECT @Statement = N'SELECT @binaryResult = ' + @TestBinHex;
EXECUTE sp_executesql @Statement, N'@binaryResult varbinary(max) OUTPUT', @binaryResult=@TestBinary OUTPUT;

SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   Base64Encoding
FROM (
    SELECT @TestBinary AS bin
) AS bin_sql_server_temp;

This was really helpful - just saved me a lot of work. Thank you — Rob P., Oct 02 '17 at 13:53
@RobP. if this solved your issue, why not marking this answer as correct? — Yahfoufi, Dec 06 '17 at 11:27
@Yahfoufi - I'm not the asker, but it did help me out a lot. — Rob P., Dec 06 '17 at 16:18

score 0 · Answer 2 · answered Jan 25 '17 at 10:44

Here's an example on a table containing a image stored as hex:

create table t (s image,s_base64 varchar(max));
insert t(s)
values(CAST('This is an image column' as image));

And here's an example on casting the hex image to base64

 create view v
 as
 select CAST(s as varbinary(max)) as s,s_base64 from t;
 GO
 update v set s_base64= CAST(N'' AS xml).value('xs:base64Binary(sql:column("v.s"))', 'varchar(max)');
 GO

 select * from v;

SQL Server : hex to base64

2 Answers2

Linked