Yesterday (or was it the day before?) I posted a function to encode base64 in SQL. Today I’m posting the function to do the reverse – to turn base64 back into ASCII.

Essentially it works in reverse of the other function. Take four characters, find their ordinal position in the @map variable (e.g. ‘T’ becomes 20) and turn those 24 bits back into 3 characters.

CREATE FUNCTION DecodeBase64(@in varchar(512))
returns varchar(256)
as
begin
declare @map varchar(64)
set @map = 'ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/'

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

set @out = ''
set @i = 1

while @i < len(@in)
begin
 set @one = CHARINDEX(SUBSTRING(@in, @i, 1) COLLATE Latin1_General_CS_AS, @map, 0)-1
 set @i = @i + 1
 set @two = CHARINDEX(SUBSTRING(@in, @i, 1) COLLATE Latin1_General_CS_AS, @map, 0)-1
 set @i = @i + 1

 if SUBSTRING(@in, @i, 1) = '='
 set @three = 0 else
 set @three = CHARINDEX(SUBSTRING(@in, @i, 1) COLLATE Latin1_General_CS_AS, @map, 0)-1
 set @i = @i + 1

 if SUBSTRING(@in, @i, 1) = '='
 set @four = 0
 set @four = CHARINDEX(SUBSTRING(@in, @i, 1) COLLATE Latin1_General_CS_AS, @map, 0)-1
 set @i = @i + 1

 -- @one, @two, @three and @four contain characters from input
 -- now convert them back to 3, 8 bit values
 declare @none int
 declare @none_1 int
 declare @none_2 int
 set @none_1 = @one * 4 -- shl 2
 set @none_2 = ((@two & 48) / 16)
 set @none = @none_1 + @none_2

 declare @ntwo int -- four bits at end of @two
 declare @ntwo_1 int
 declare @ntwo_2 int
 set @ntwo_1 = (@two & 15) * 16
 set @ntwo_2 = ((@three & 60) / 4)
 set @ntwo = @ntwo_1 + @ntwo_2

 declare @nthree int
 declare @nthree_1 int
 declare @nthree_2 int
 set @nthree_1 = (@three & 3) * 64
 set @nthree_2 = @four
 set @nthree = @nthree_1 + @nthree_2

 if @nthree > 0
 set @out = @out + CHAR(@none) + CHAR(@ntwo) + CHAR(@nthree)
 else if @ntwo > 0
 set @out = @out + CHAR(@none) + CHAR(@ntwo)
 else
 set @out = @out + CHAR(@none)

 --print substring(@map, @none, 1)
 -- add on first two bits from second 0x3F
end

return @out

end