Base64 encoding in SQL Server 2005 T-SQL

by

I know this has already been answered, but I just spent more time than I care to admit coming up with single-line SQL statements to accomplish this, so I’ll share them here in case anyone else needs to do the same:

-- Encode the string "TestData" in Base64 to get "VGVzdERhdGE="
SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   Base64Encoding
FROM (
    SELECT CAST('TestData' AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;

-- Decode the Base64-encoded string "VGVzdERhdGE=" to get back "TestData"
SELECT 
    CAST(
        CAST(N'' AS XML).value(
            'xs:base64Binary("VGVzdERhdGE=")'
          , 'VARBINARY(MAX)'
        ) 
        AS VARCHAR(MAX)
    )   ASCIIEncoding
;

I had to use a subquery-generated table in the first (encoding) query because I couldn’t find any way to convert the original value (“TestData”) to its hex string representation (“5465737444617461”) to include as the argument to xs:hexBinary() in the XQuery statement.

I hope this helps someone!

More Related Contents:

  1. Retrieving last record in each group from database – SQL Server 2005/2008
  2. How do I perform an accent insensitive compare (e with è, é, ê and ë) in SQL Server?
  3. Calculating timespan with t-sql
  4. Isoweek in SQL Server 2005
  5. Aggregate SQL Function to grab only the first from each group
  6. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  7. Create a date from day month and year with T-SQL
  8. Integer division in sql server
  9. Base64 Encode String in VBScript
  10. Retrieving binary file content using Javascript, base64 encode it and reverse-decode it using Python
  11. How much size “Null” value takes in SQL Server
  12. SQL – Subtracting a depleting value from rows
  13. How do I base64 encode a string efficiently using Excel VBA?
  14. SELECT FOR UPDATE with SQL Server
  15. Easiest way to populate a temp table with dates between and including 2 date parameters
  16. How do I move files to an archive folder after the files have been processed?
  17. WHERE clause on SQL Server “Text” data type
  18. SQL Server error on update command – “A severe error occurred on the current command”
  19. SQL Dynamic Pivot – how to order columns
  20. Foreign key relationship with composite primary keys in SQL Server 2005
  21. Unique Key Violation in SQL Server – Is it safe to assume Error 2627?
  22. SQL server schema and default schema
  23. How to recover deleted rows from SQL server table?
  24. SQL Server 2005 – using generated sequences instead of Identity columns?
  25. OPENXML with xmlns:dt
  26. Compute MD5 hash of a UTF8 string
  27. FOR XML PATH(”): Escaping “special” characters
  28. Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
  29. Compare dates in T-SQL, ignoring the time part
  30. Multiple Select Statements using SQL Server 2005 “WITH” Statement

Leave a Comment