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.
For the sake of this demo we will create the folder: C:\Ariely_BASE46_VARCHAR
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
USE tempdb
GO
DROP
TABLE
IF EXISTS AriTestTbl
CREATE
AriTestTbl(
Id
int
identity(1,1)
primary
key
,
AvatarBinary VARBINARY(
MAX
),
AvatarBase64
VARCHAR
(
)
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
*
AriTestTbl
OK, now we can start our discussion and demo...
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.
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.
The basic logic of this action based on:
----------------------- Get value
DECLARE
@B VARBINARY(
@B = col
----------------------- Convert to BASE64
select
from
openjson(
@B
as
col) T
for
json auto
with
(col
varchar
max
))
Go
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
cast
''
xml).value(
'xs:base64Binary(sql:variable("@B"))'
'varchar(max)'
'*'
) Tbl
xml path(
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.
Id,AvatarBinary
(Id
, AvatarBinary
Id,
'xs:base64Binary(sql:column("AriTestTbl.AvatarBinary"))'
Id,AvatarBinary,s
cross
apply (
AvatarBinary
)) T (s)
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.
@S
----------------------- Get Binary value
----------------------- Get the result of Convert Binary to BASE64 String
@S = col
----------------------- Convert BASE64 String back to Binary
@B Original,
@S Base64_String,
CAST
(N
AS
'xs:base64Binary(sql:variable("@S"))'
'varbinary(max)'
) Result_Varbinary
First we will fill the table with the BASE64 values of the images.
UPDATE
SET
AvatarBase64 = s
and now, we can see example of converting the data
AriTestTbl.AvatarBase64,
'xs:base64Binary(sql:column("AriTestTbl.AvatarBase64"))'
column_varbinary
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.
This article is also available the following languages: