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|[email protected]|
|456|[email protected]|

Now I want output like below :

|Id |EmailID       |Base64String                 |
|abc|[email protected]|Base64 string of (Id+EmailID)|
|xyz|[email protected]|Base64 string of (Id+EmailID)|

Any answers will greatly appreciated.

Thanks in Advance.

**[Base64 encoding in SQL Server](http://stackoverflow.com/questions/5082345/base64-encoding-in-sql-server-2005-t-sql)** — Lukasz Szozda, Apr 04 '16 at 14:08

score 0 · Answer 1 · answered Apr 04 '16 at 14:42

;WITH cte AS (
SELECT *
FROM (VALUES
(123, '[email protected]'),
(456, '[email protected]')
) 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         [email protected] MQAyADMAdABlAHMAdAAxAEAAdABlAHMAdAAuAGMAbwBtAA==
456         [email protected] NAA1ADYAdABlAHMAdAAyAEAAdABlAHMAdAAuAGMAbwBtAA==

(2 row(s) affected)

How to convert entire column to Base64 string?

1 Answers1