Basic Encoding/Decoding using Base64 in SQL Server

Basically many of the projects require Base64 Encoding and Decoding for their projects for basic URL Encoding and Decoding. Sometimes the data need to store in the Base64 format in the DB and while fetching need to decode from the Base64 format. The below SQL Server Functions will deserve the basic encoding and decoding features for the novice users.

— =================================================

— Author:  V.U.M.Sastry Sagi

— Create date: 09/24/2010

— Description: Encrypts the string to Base64 Format

— =================================================

CREATE FUNCTION ToBase64 (@Input varchar(6000))

RETURNS varchar(8000)

AS

BEGIN

DECLARE

@Output varchar(8000),

@Bits varbinary(3),

@Pos int

SET @Pos = 1

SET @Output = ”

WHILE @Pos <= Len(@Input) BEGIN

SET @Bits = Convert(varbinary(3), Substring(@Input, @Pos, 3))

SET @Output = @Output +

Substring(‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/’,

Substring(@Bits, 1, 1) / 4 + 1, 1)

SET @Output = @Output +

Substring(‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/’,

Substring(@Bits, 1, 1) % 4 * 16 +

Substring(@Bits, 2, 1) / 16 + 1, 1)

SET @Output = @Output +

Substring(‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/’,

Substring(@Bits, 2, 1) % 16 * 4 +

Substring(@Bits, 3, 1) / 64 + 1, 1)

SET @Output = @Output +

Substring(‘ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/’,

Substring(@Bits, 3, 1) % 64 + 1, 1)

SET @Pos = @Pos + 3

END

RETURN (Left(@Output, Len(@Output) – 3 +

Len(@Bits)) + Replicate(‘=’, 3 – Len(@Bits)))

END

— ===================================================

— Author:  V.U.M.Sastry Sagi

— Create date: 09/24/2010

— Description: Decrypts the string from Base64 Format

— ===================================================

CREATE FUNCTION FromBase64 (@data varchar(max))

RETURNS varchar(8000)

AS

BEGIN

DECLARE

@Output varchar(8000),

@Bits varbinary(3)

— declare vars.

DECLARE @XmlData xml

— construct an xml var.

SET @XmlData = CAST(‘<data>’ + @data + ‘</data>’ as xml)

— base64 decode the @data.

SELECT @Output= CONVERT(varchar(max),

@XmlData.value(‘(data)[1]’, ‘varbinary(max)’))

RETURN @Output

END