BASE64 Encode and Decode in T-SQL

Question

Post reply

BASE64 Encode and Decode in T-SQL

Daniel Payne

Valued Member

Points: 63
More actions
October 10, 2007 at 6:14 am

#178830

Comments posted to this topic are about the item BASE64 Encode and Decode in T-SQL

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ManicStar SSCoach Points: 17992 More actions · Answer 1

I know this was posted a while ago, but its very useful when hacking Oracle WCC paths.

Thank you!

Carlos Alejandro Pizzi-361990 Newbie Points: 2 More actions · Answer 2

Thank you very much for the contribution, it has helped me a lot.

Thank you!

Steve Collins SSCommitted Points: 1912 More actions · Answer 3

For posterity since 2012 the built-in method for converting to base64 is using XML. This comes up every now and then on SSC. The only post I could find tho was from Jonathon

https://www.sqlservercentral.com/forums/topic/how-to-create-a-calculated-column-that-encodes-in-base64

From my SQL script of online stuff I found this example(which had to do with salting/unsalting a string)

declare
  @salt   char(36)=cast(newid() as char(36)),
  @string char(36)=cast(newid() as char(36));

select @salt salt, @string string;

declare
  @string_to_convert  varchar(max)=concat(@salt, @string),
  @binary_base64      varchar(max),
  @unconverted_string varchar(max);

/* convert from varchar to binary base 64 (varchar) */  
select @binary_base64=(select cast(@string_to_convert as varbinary(max)) for xml path(''), binary base64);
select @binary_base64 converted_string;

/* convert from binary base 64 (varchar) to varchar */  
select @unconverted_string=(select cast(cast(@binary_base64 as xml).value('.','varbinary(max)') as varchar(max)));
select left(@unconverted_string, 36) salt, right(@unconverted_string, 36) string;

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können