Friday, July 21, 2017

Transact-SQL: Convert VARBINARY to Base64 String and vice versa

Introduction

Transact-SQL does not include built in function to convert binary type into base64 string and vice versa, but it does includes several built-in options we can use. This article demonstrates several options to convert VARBINARY data into Base64 string, and vice versa convert Base64 string into VARBINARY. 

Table of Contents

Preparation

1. Create folder

For the sake of this demo we will create the folder: C:\Ariely_BASE46_VARCHAR

2. insert image

we will need a simple image for our demo. For the sake of this demo we will use your personal avatar image from your MSDN profile. 
Go to your profile -> right click on your avatar image -> and save the image in the new folder, which we created.
The avatar image named avatar.jpg

3. Create new table

USE tempdb
GO
  
DROP TABLE IF EXISTS AriTestTbl
GO
  
CREATE TABLE AriTestTbl(
    Id int identity(1,1) primary key,
    AvatarBinary VARBINARY(MAX),
    AvatarBase64 VARCHAR(MAX)
)
GO
4. Import the image into the database as binary data
* Let's insert the same image 3 times, simply to give us several rows for the demo.
Insert AriTestTbl(AvatarBinary)
SELECT col FROM OPENROWSET(
    BULK N'C:\Ariely_BASE46_VARCHAR\avatar.jpg', SINGLE_BLOB
) Tbl(col)
GO 3
   
SELECT * FROM AriTestTbl
GO
OK, now we can start our discussion and demo...

Convert VARBINARY to Base64 String

we can use XML built-in options and JSON built-in (from SQL Server 2016) functions, in order to convert VARBINARY into Base64. Using variables is a bit different from working with tables. Therefore, we will show both options in separate sections.

Converting Variables from VARBINARY to Base64

In this section we will use simple select from OPENROWSET query (same way we imported the image to the table), in order to configure the value of our VARBINARY variable.

Option 1: Convert binary to Base64 using JSON

The basic logic of this action based on:
  • We select the value as table
  • We convert the data in the table into JSON using the built-in hint "FOR JSON AUTO". This operating will automatically convert any binary data type in the table into BASE64 String.
  • Next, Using the function OPENJSON, we extract the BASE64 String from the JSON
----------------------- Get value
DECLARE @B VARBINARY(MAX)
SELECT @B = col
FROM OPENROWSET(
BULK N'C:\Ariely_BASE46_VARCHAR\avatar.jpg', SINGLE_BLOB
) Tbl(col)
----------------------- Convert to BASE64
select col
from openjson(
    (
        select col
        from (SELECT @B as col) T
        for json auto
    )
) with(col varchar(max))
Go

Option 2: Convert binary to Base64 using XML XQuery

XQuery (XML Query) is a query language designed to query XML data. Transact-SQL supports a subset of the XQuery language, which can be used for querying xml data type. For more information you can check the links
----------------------- Get value
DECLARE @B VARBINARY(MAX)
SELECT @B = col
FROM OPENROWSET(
BULK N'C:\Ariely_BASE46_VARCHAR\avatar.jpg', SINGLE_BLOB
) Tbl(col)
----------------------- Convert to BASE64
select cast('' as xml).value('xs:base64Binary(sql:variable("@B"))', 'varchar(max)')
GO

Option 3: Convert binary to Base64 using XML and the hint "for xml path"

----------------------- Get value
DECLARE @B VARBINARY(MAX)
SELECT @B = col
FROM OPENROWSET(
BULK N'C:\Ariely_BASE46_VARCHAR\avatar.jpg', SINGLE_BLOB
) Tbl(col)
----------------------- Convert to BASE64
select * from (select @B as '*') Tbl
for xml path('')
GO

Converting Column values from VARBINARY to Base64

In most cases we will need to work on multiple rows in table, and we want to convert only the VARBINARY data into BASE64 String. The basic solution is the same as above, except for the solution using XML XQuery, which we will simply need to use different method.

Option 1: Convert binary to Base64 using JSON

select Id,AvatarBinary
from openjson(
    (
        select Id,AvatarBinary
        from AriTestTbl
        for json auto
    )
) with(Id int, AvatarBinary varchar(max))
GO

Option 2: Convert binary to Base64 using XML XQuery

select Id,
    cast('' as xml).value(
        'xs:base64Binary(sql:column("AriTestTbl.AvatarBinary"))', 'varchar(max)'
    )
from AriTestTbl
GO

Option 3: Convert binary to Base64 using XML and the hint "for xml path"

select Id,AvatarBinary,s
from AriTestTbl
cross apply (select AvatarBinary as '*' for xml path('')) T (s)
GO

Convert Base64 String to VARBINARY

Basically for each option demonstrated above, there is an equivalent option to convert the data in the opposite direction. For the sake of this article, we will focus on one example at this time.

Converting Variables from Base64 to VARBINARY

DECLARE @B VARBINARY(MAX)
DECLARE @S VARCHAR(MAX)
----------------------- Get Binary value
SELECT @B = col
FROM OPENROWSET(
BULK N'C:\Ariely_BASE46_VARCHAR\avatar.jpg', SINGLE_BLOB
) Tbl(col)
----------------------- Get the result of Convert Binary to BASE64 String
select @S = col
from openjson(
    (
        select col
        from (SELECT @B as col) T
        for json auto
    )
) with(col varchar(max))
----------------------- Convert BASE64 String back to Binary
SELECT
    @B Original,
    @S Base64_String,
    CAST(N'' AS xml).value(
        'xs:base64Binary(sql:variable("@S"))', 'varbinary(max)'
    ) Result_Varbinary
GO

Converting column values from Base64 to VARBINARY

First we will fill the table with the BASE64 values of the images.
UPDATE AriTestTbl SET AvatarBase64 = s
from AriTestTbl
cross apply (select AvatarBinary as '*' for xml path('')) T (s)
GO
  
SELECT * FROM AriTestTbl
GO
and now, we can see example of converting the data
SELECT AriTestTbl.AvatarBase64, CAST(N'' AS xml).value(
    'xs:base64Binary(sql:column("AriTestTbl.AvatarBase64"))'
    , 'varbinary(max)'
) AS column_varbinary
FROM AriTestTbl
GO

In conclusion

As you can see in the above demo it is very simple to convert both direction between Base64 String and VARBINARY, using built-in XML and JSON features. 

You might ask yourself, why do we need these type of actions in practice, in the SQL Server side. Won't is be better to do it in your application (client) side... Well, in most cases the answer is yes, but for example, in lot of cases we want to senfd email using the built-in stored procedure sp_send_dbmail. Using Base64 string we can include embedded images, which stored in the database as VARBINARY,  in our email.

Posted by at No comments:

SQL Server has built-in support for base64 encoding and decoding

Did you know that SQL Server has built-in support for base64 encoding and decoding and has since 2005? I sure didn’t, because it’s hidden in the depths of XQuery. Here is a complete sample showing a complete round trip from string to binary to base64, back to binary and back to string again.

declare @source varbinary(max), @encoded varchar(max), @decoded varbinary(max)

 set @source = convert(varbinary(max), 'Hello Base64')
set @encoded = cast('' as xml).value('xs:base64Binary(sql:variable("@source"))', 'varchar(max)')
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable("@encoded"))', 'varbinary(max)')

 select
convert(varchar(max), @source) as source_varchar,
@source as source_binary,
@encoded as encoded,
@decoded as decoded_binary,
convert(varchar(max), @decoded) as decoded_varchar

Posted by at No comments:
Subscribe to: Posts (Atom)