msSql 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 :/

Answer 1

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;