Results 1 to 5 of 5

Thread: SQL Server 2005 - Convert any data to Base64 and back

Threaded View

  1. #1

    Thread Starter
    PowerPoster
    Join Date
    Feb 2002
    Location
    Canada, Toronto
    Posts
    5,802

    SQL Server 2005 - Convert any data to Base64 and back

    Even though the actual conversion is a one line code, it is still better to have it in a function (at least you don't have to remember all that XML stuff...)

    The first function f_BinaryToBase64 takes binary data as input, but anything can be converted to binary.

    The second function f_Base64ToBinary takes the Base64 data (the output of the first function), and outputs the original binary data (that can be converted back to your data type).

    How to use with strings for example:
    sql Code:
    1. DECLARE @Base64Data VARCHAR(MAX)
    2. SET @Base64Data = dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX),'testing...'))
    3.  
    4. SELECT @Base64Data AS Base64, CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary(@Base64Data)) AS My_Data

    And the actual functions:
    sql Code:
    1. -- =============================================
    2. -- Author:      <Michael Ciurescu>
    3. -- Create date: <20090123>
    4. -- Description: <Description, ,>
    5. -- =============================================
    6. CREATE FUNCTION dbo.f_BinaryToBase64
    7. (
    8.     @bin VARBINARY(MAX)
    9. )
    10. RETURNS VARCHAR(MAX)
    11. AS
    12. BEGIN
    13.     DECLARE @Base64 VARCHAR(MAX)
    14.    
    15.     /*
    16.         SELECT dbo.f_BinaryToBase64(CONVERT(VARBINARY(MAX), 'Converting this text to Base64...'))
    17.     */
    18.    
    19.     SET @Base64 = CAST(N'' AS XML).value('xs:base64Binary(xs:hexBinary(sql:variable("@bin")))', 'VARCHAR(MAX)')
    20.    
    21.     RETURN @Base64
    22. END
    23.  
    24.  
    25. -- =============================================
    26. -- Author:      <Michael Ciurescu>
    27. -- Create date: <20090123>
    28. -- Description: <Description, ,>
    29. -- =============================================
    30. CREATE FUNCTION dbo.f_Base64ToBinary
    31. (
    32.     @Base64 VARCHAR(MAX)
    33. )
    34. RETURNS VARBINARY(MAX)
    35. AS
    36. BEGIN
    37.     DECLARE @Bin VARBINARY(MAX)
    38.    
    39.     /*
    40.         SELECT CONVERT(VARCHAR(MAX), dbo.f_Base64ToBinary('Q29udmVydGluZyB0aGlzIHRleHQgdG8gQmFzZTY0Li4u'))
    41.     */
    42.    
    43.     SET @Bin = CAST(N'' AS XML).value('xs:base64Binary(sql:variable("@Base64"))', 'VARBINARY(MAX)')
    44.  
    45.     RETURN @Bin
    46. END
    Last edited by CVMichael; Jan 24th, 2009 at 07:10 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width