Base64 Encode / Decode

November 19, 2007 at 12:04 am · Filed under SQL Server

SQL Server 2005 does not provide specific functions for Base64 encoding / decoding, but you can create them easily enough by leveraging the XML functionality.

CREATE FUNCTION [dbo].[base64_encode] (@data VARBINARY(max)) RETURNS VARCHAR(max)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
BEGIN
    RETURN (
        SELECT [text()] = @data
            FOR XML PATH(”)
    )
END
GO

CREATE FUNCTION [dbo].[base64_decode] (@base64_text VARCHAR(max)) RETURNS VARBINARY(max)
    WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
BEGIN
    DECLARE @x XML; SET @x = @base64_text
    RETURN @x.value(‘(/)[1]‘, ‘VARBINARY(max)’)
END
GO

If throughput is a concern, equivalent CLR-based implementations would likely have preferable performance characteristics.

1 Comment

  1. Just said,

    March 13, 2008 @ 5:08 am

    This is awesome!
    For a couple of hours I was googling for something like that and saw so many stupid “solutions” so I thought I would never find what I wanted. I tried to make on my own something like select for xml raw, binary base64, and it was also stupid. Now I see I lack for a lot of useful knowledge, and I think the next book I will read will be “MCTS Self–Paced Training Kit (Exam 70–431): Implementing and Maintaining Microsoft SQL Server 2005″.

    Thank you for your laconic and nevertheless indeed effective code!

RSS feed for comments on this post