Wednesday, 21 June 2017

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.

4 comments: