How to convert entire column to Base64 string?

Question

I want to convert entire column of a table to Base64 string.

For example I have below table :

|Id |EmailID       |
|123|test1@test.com|
|456|test2@test.com|

Now I want output like below :

|Id |EmailID       |Base64String                 |
|abc|test1@test.com|Base64 string of (Id+EmailID)|
|xyz|test2@test.com|Base64 string of (Id+EmailID)|

Any answers will greatly appreciated.

Thanks in Advance.

Answer 1

;WITH cte AS (
SELECT *
FROM (VALUES
(123, 'test1@test.com'),
(456, 'test2@test.com')
) AS t(Id, EmailID)
)

SELECT  Id,
        EmailID,
        CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:column("bin")))', 'NVARCHAR(MAX)') as Base64String
FROM (
    SELECT Id, EmailID, CAST(CAST(Id as nvarchar(10)) + EmailID AS VARBINARY(MAX)) AS bin
    FROM cte
) as t

Output:

Id          EmailID        Base64String
----------- -------------- ------------------------------------------------
123         test1@test.com MQAyADMAdABlAHMAdAAxAEAAdABlAHMAdAAuAGMAbwBtAA==
456         test2@test.com NAA1ADYAdABlAHMAdAAyAEAAdABlAHMAdAAuAGMAbwBtAA==

(2 row(s) affected)