Recently I had need of a SQL routine to do base64 encoding. It turned out to be an easier task than I had anticipated, given that SQL has no bit shifting capabilities. The code is below. There is no decode routine yet, but writing one should be equally trivial.

Base64 encoding basically works by taking the ASCII values of three consecutive characters (24 bits in total) and turning it into 4 sets of 6 bits (making the highest number possible 63) and then using those 4 numbers as the index into an array of characters which become the encoded text. Where less than three characters are left at the end of the input string, 0 is used for padding and then those characters in the output text are replaced with =.

So ‘ABC’ in ASCII as binary = 01000001 01000010 01000011

Split that into 4 lots of 6 bits and you get

010000 010100 001001 000011

or 16, 20, 9, 3 in decimal.

You then use 16, 20, 9 and 3 as the index into the array:

ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/

and you get QUJD.

CREATE FUNCTION EncodeBase64(@in nvarchar(256))
RETURNS VARCHAR(512)
as
BEGIN

declare @map varchar(64)
set @map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'

declare @out varchar(512)
declare @i int
declare @rem int
declare @one int, @two int, @three int

set @i = 1
set @out = ''

set @rem = len(@in)
while @rem > 0
begin
 if @rem < 2
 begin
 set @one = ascii(substring(@in, @i, 1))
 set @i = @i + 1
 set @two = 0
 set @i = @i + 1
 set @three = 0
 set @i = @i + 1
 end    else
 if @rem < 3
 begin
 set @one = ascii(substring(@in, @i, 1))
 set @i = @i + 1
 set @two = ascii(substring(@in, @i, 1))
 set @i = @i + 1
 set @three = 0
 set @i = @i + 1
 end else
 begin
 set @one = ascii(substring(@in, @i, 1))
 set @i = @i + 1
 set @two = ascii(substring(@in, @i, 1))
 set @i = @i + 1
 set @three = ascii(substring(@in, @i, 1))
 set @i = @i + 1
 end

 -- Got the three int values, now turn them into 4
 -- take the first 6 bits of @one

 declare @none int
 set @none = (@one & 252) -- first 6 bits of @one (leftmost)
 set @none = @none / 4

 declare @ntwo int
 declare @two_1 int -- left over 2 bits from @one
 set @two_1 = @one & 3

 declare @two_2 int
 set @two_2 = @two & 240    -- first 4 bits of @two
 set @two_2 = @two_2 / 16
 set @ntwo = (@two_1 * 16) + @two_2

 declare @nthree int
 declare @three_1 int
 set @three_1 = @two & 15 -- left over 4 bits from @two
 declare @three_2 int
 set @three_2 = @three & 196 -- first 2 bits of @three
 set @three_2 = @three_2 / 64
 set @nthree = (@three_1 * 4) + @three_2

 declare @nfour int
 set @nfour = @three & 63

 declare @c1 varchar(1), @c2 varchar(1), @c3 varchar(1), @c4 varchar(1)

 if @none > 0
 set @c1 = substring(@map, @none+1, 1)
 else set @c1 = '='

 if @ntwo > 0
 set @c2 = substring(@map, @ntwo+1, 1)
 else set @c2 = '='

 if @nthree > 0
 set @c3 = substring(@map, @nthree+1, 1)
 else set @c3 = '='

 if @nfour > 0
 set @c4 = substring(@map, @nfour+1, 1)
 else set @c4 = '='

 set @out = @out + @c1 + @c2 + @c3 + @c4

 set @rem = len(@in) - @i + 1
end

return @out

end

This is not the best, or even only, way of accomplishing this, but this is my way of doing it