Base64 Encoding And Decoding In SQL Server

Introduction
In this article we will learn How to encode a string into Base64String and how to decode Base64String to string. How to create a function for converting Base64 to string . How to create a function string to Base64 in sql server. Function for encode string to Base64 in sql server/ Function for edecode Base64string to String in sql server.

Previous Updates

In previous articles we have learnt Best basic C# interview questions. Transform multiple rows into one comma separated string column. .AngularJS basics, What is Cursor in sql and use of cursor. 

Description

The Encoding and Decoding of a string function are very usefull in Sql Server. Many times there is a need to encrypt or decrypt password some special column values in sql then you can use this sql code and achieve your objective with in a minute.

Convert String To Base64

CREATE FUNCTION [dbo].[fn_string_To_BASE64] (     @inputString NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN     RETURN (         SELECT             CAST(N'' AS XML).value(                   'xs:base64Binary(xs:hexBinary(sql:column("bin")))'                 , 'NVARCHAR(MAX)'             )   Base64Encoding         FROM (             SELECT CAST(@inputString AS VARBINARY(MAX)) AS bin         ) AS bin_sql_server_temp     ) END

Convert Base64 To String

CREATE FUNCTION [dbo].[fn_BASE64_To_String] (     @BASE64_STRING NVARCHAR(MAX) ) RETURNS NVARCHAR(MAX) AS BEGIN     RETURN (         SELECT             CAST(                 CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@BASE64_STRING"))', 'VARBINARY(MAX)')             AS NVARCHAR(MAX)             )   UTF8Encoding     ) END

Execute Both Function 

SELECT [dbo].[fn_string_To_BASE64](Test String) SELECT dbo.fn_BASE64_To_String(dbo.fn_string_To_BASE64(Test String'))

If you are sending your encoded string directly from your code the you can achieve this task by the given way :-

DECLARE @source VARCHAR(MAX), @encoded VARCHAR(MAX), @decoded VARCHAR(MAX) SET @source = 'Test String' SET @encoded = (SELECT dbo.fn_string_To_BASE64(@source)) SET @decoded = (SELECT dbo.fn_BASE64_To_String(@encoded)) SELECT @source AS InputString , @encoded As Encoded_Value , @decoded As Decoded_Value

Your Output Looks Similar To This -

Hope this update will helps you a lot.