Insert base 64 string into SQL Server database

Question

I get a base 64 string from a XML file which I want to insert into my SQL Server database. Which field type have the field in my database to be? varbinary(MAX)? Do I have to convert the base 64 string to another format before inserting into my database?

Best regards

Answer 1

If you intend to store your Base64 string as is, you can use the VARCHAR data type. The Base64 encoding has been devised to use only 7-bit ASCII characters.

However, if you prefer to store your data in binary format, you would need to use the VARBINARY data type and convert your Base64 string to binary. You can use the XQuery functionality (SQL Server 2005 onwards) to easily convert values to VARBINARY and vice-versa.

Convert Base64 value in a variable to VARBINARY:

declare @str varchar(20);

set @str = '3qAAAA==';

select cast(N'' as xml).value('xs:base64Binary(sql:variable("@str"))', 'varbinary(20)');

Convert binary value in a variable to Base64:

declare @bin varbinary(20);

set @bin = 0xDEA00000;

select cast(N'' as xml).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'varchar(20)');

Source (and more examples): Converting from Base64 to varbinary and vice versa.

Answer 2

Since it's a string and a string is a string is a string, you should be able to put it into a VARCHAR(x) or VARCHAR(MAX) field without any problems.

The "sized" VARCHAR(x) has a max. length of 8000 characters, while VARCHAR(MAX) tops out at 2 GB (2**31-1 byte) of size.

Answer 3

It's worth pointing out that if you use varchar(x) or varchar(max) with base64 strings and you use the base64 string in any WHERE clauses for lookups you should use a case sensitive collation on the column because case is significant with base64 encoded data.

Answer 4

varbinary(MAX) would be the most efficient storage medium (raw bytes are smaller than b64 encoded), but you'd have to convert from b64 to raw bytes. If you want to store the b64 as you get it, just use varchar(max). Really depends- if you're going to splat it right back into XML, it'd be less processing to leave the b64 alone.