BASE64 Encode and Decode in T-SQL

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Aaron West » BASE64 Encode and Decode in T-SQL - optimized...

BASE64 Encode and Decode in T-SQL - optimized

Rate Topic

Display Mode

Topic Options

Author

Message

Aaron West

Posted Saturday, October 13, 2007 8:57 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:14 PM
Points: 142, Visits: 124

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

Post #410316

dpassey

Posted Wednesday, November 12, 2008 11:24 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, January 11, 2012 12:32 PM
Points: 1, Visits: 3

I came across a bug(?) in this function. I'm using it in my proc to decode the base64 encoded string. My string is this:

'This is a really long sentence to see how this thing will crash or if it even will crash I am now at 152 letters and still conting'.

It get encoded via an Actionscript function to this:

'VGhpcyBpcyBhIHJlYWxseSBsb25nIHNlbnRlbmNlIHRvIHNlZSBob3cgdGhpcyB0aGluZyB3aWxs
IGNyYXNoIG9yIGlmIGl0IGV2ZW4gd2lsbCBjcmFzaCBJIGFtIG5vdyBhdCAxNTIgbGV0dGVycyBh
bmQgc3RpbGwgY29udGluZy4='

What the proc sees is this:

This is a really long sentence to see how this thing willò7&6‚÷"–b—BWfVâv–ÆÂ7&6‚’Òæ÷rBS"ÆWGFW'2P¿nd still conting.

For some reason the function bombs at around the 59th place in the string but then picks back up later. Any ideas why? The column is defined as varchar(500) so no insert/update problems there.

Thanks.

Post #601583

lyout

Posted Tuesday, March 17, 2009 8:01 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 17, 2009 7:58 PM
Points: 1, Visits: 0

Thanks!
It's very useful for me.

Post #678047

ZA_Crafty

Posted Wednesday, April 14, 2010 11:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 18, 2012 1:28 AM
Points: 161, Visits: 233

Thanks for this. I nearly used this function.

Post #903759

Aaron West

Posted Wednesday, January 16, 2013 2:05 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:14 PM
Points: 142, Visits: 124

Remove whitespace before calling the function. Use SUBSTITUTE

If you uncomment this code it would return null:

--IF @encoded_text COLLATE LATIN1_GENERAL_BIN
-- LIKE '%[^ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789+/=]%'
-- COLLATE LATIN1_GENERAL_BIN
-- RETURN NULL

try:
SET @encoded_text = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(
@encoded_text, ' ',''),CHAR(9),''),CHAR(10),''),CHAR(13),'')

(Okay so 5 years for me to bother to check the discussion is a bit long. I use Oracle lately...)

Post #1408075

« Prev Topic | Next Topic »

Permissions