How to apply base64 encoding in sql?

1

I have been working with sql for a long time. But never tried to do base64 encoding in sql server. I need to apply base64 encoding in one of the value in sql server table. When I tried to apply base64 encoding in sql, it's giving me different result. Here's an example:
WORKSITE
!nrtdms:0:!session:WORKSITE:!database:GHMatters:!document:2287888,1:
[Version]
[Latest=N]

When I apply base64 encoding on the above value in sql server I get the below output -

VwBPAFIASwBTAEkAVABFAAoAIQBuAHIAdABkAG0AcwA6ADAAOgAhAHMAZQBzAHMAaQBvAG4AOgBXAE8AUgBLAFMASQBUAEUAOgAhAGQAYQB0AGEAYgBhAHMAZQA6AEcASABNAGEAdAB0AGUAcgBzADoAIQBkAG8AYwB1AG0AZQBuAHQAOgAyADIAOAA3ADgAOAA4ACwAMQA6AA0ACgBbAFYAZQByAHMAaQBvAG4AXQANAAoAWwBMAGEAdABlAHMAdAA9AE4AXQA=

But the correct encoding value is:
V09SS1NJVEUKIW5ydGRtczowOiFzZXNzaW9uOldPUktTSVRFOiFkYXRhYmFzZTpHSE1hdHRlcnM6IWRvY3VtZW50OjIyODc4ODgsMToNCltWZXJzaW9uXQ0KW0xhdGVzdD1OXQ0K

Here's the sql function for encoding:

(
    @input NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @base64 NVARCHAR(MAX);

    -- Convert the input string to binary
    DECLARE @binary VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @input);

    -- Encode the binary data to Base64
    SET @base64 = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@binary"))', 'VARCHAR(MAX)');

    RETURN @base64;
END;

Anyone here to suggest a solution for this?

2

Your function is taking in input as nvarchar, meaning Unicode. Base64 will generate a different output because the binary representation is different for varchar and nvarchar.

3

Thanks @robert_volk . I have figured it out after I posted this. Here's the updated function if this helps someone in future -

CREATE FUNCTION [dbo].[EncodeToBase64] (@DocumentNumber VARCHAR(20))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Input VARCHAR(MAX) = @DocumentNumber
DECLARE @base64 NVARCHAR(MAX);

-- Convert the input string to binary
DECLARE @binary VARBINARY(MAX) = CONVERT(VARBINARY(MAX), @Input);

-- Encode the binary data to Base64
SELECT @base64 = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@binary"))', 'VARCHAR(MAX)');

RETURN @base64;

END;