Native Base64 decode function in SAP HANA

Recently I had to work around some limitations in SAP Business One’s Service Layer. As a result, I had to decode some BASE64 string that was stored in our customer’s database, modify the (unencoded) string and then save it back, all from within a native SAP HANA Stored Procedure.

To my surprise, there is no native function to achieve this, at least not that I could find in my couple of hours of investigation.

So I set out to build my own function to allow for decoding Base64 strings on the database level.

I am posting this function here, so that others can save themselves a few hours if they are ever presented with the same problem.

Note: This is probably not the most performant way of doing this, but since it’s not going to be executed massively it is good enough for our purpose. The function returns the decoded value of a 400-character string in 1-2 ms on our test server. If you do have have performance improvements, please share!

CREATE OR REPLACE FUNCTION "CEODO_FN_Base64Decode"

(

base64 varchar(8000)

)

RETURNS decoded nvarchar(4000)

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

DETERMINISTIC

AS

BEGIN

declare i int;

declare binstring varchar(16);

binstring = '';

decoded = '';

FOR i IN 1..LENGTH(:base64) DO

binstring := binstring ||

CASE 

SUBSTRING(:base64, i, 1)

WHEN 'A' THEN '000000'

WHEN 'B' THEN '000001'

WHEN 'C' THEN '000010'

WHEN 'D' THEN '000011'

WHEN 'E' THEN '000100'

WHEN 'F' THEN '000101'

WHEN 'G' THEN '000110'

WHEN 'H' THEN '000111'

WHEN 'I' THEN '001000'

WHEN 'J' THEN '001001'

WHEN 'K' THEN '001010'

WHEN 'L' THEN '001011'

WHEN 'M' THEN '001100'

WHEN 'N' THEN '001101'

WHEN 'O' THEN '001110'

WHEN 'P' THEN '001111'

WHEN 'Q' THEN '010000'

WHEN 'R' THEN '010001'

WHEN 'S' THEN '010010'

WHEN 'T' THEN '010011'

WHEN 'U' THEN '010100'

WHEN 'V' THEN '010101'

WHEN 'W' THEN '010110'

WHEN 'X' THEN '010111'

WHEN 'Y' THEN '011000'

WHEN 'Z' THEN '011001'

WHEN 'a' THEN '011010'

WHEN 'b' THEN '011011'

WHEN 'c' THEN '011100'

WHEN 'd' THEN '011101'

WHEN 'e' THEN '011110'

WHEN 'f' THEN '011111'

WHEN 'g' THEN '100000'

WHEN 'h' THEN '100001'

WHEN 'i' THEN '100010'

WHEN 'j' THEN '100011'

WHEN 'k' THEN '100100'

WHEN 'l' THEN '100101'

WHEN 'm' THEN '100110'

WHEN 'n' THEN '100111'

WHEN 'o' THEN '101000'

WHEN 'p' THEN '101001'

WHEN 'q' THEN '101010'

WHEN 'r' THEN '101011'

WHEN 's' THEN '101100'

WHEN 't' THEN '101101'

WHEN 'u' THEN '101110'

WHEN 'v' THEN '101111'

WHEN 'w' THEN '110000'

WHEN 'x' THEN '110001'

WHEN 'y' THEN '110010'

WHEN 'z' THEN '110011'

WHEN '0' THEN '110100'

WHEN '1' THEN '110101'

WHEN '2' THEN '110110'

WHEN '3' THEN '110111'

WHEN '4' THEN '111000'

WHEN '5' THEN '111001'

WHEN '6' THEN '111010'

WHEN '7' THEN '111011'

WHEN '8' THEN '111100'

WHEN '9' THEN '111101'

WHEN '+' THEN '111110'

WHEN '/' THEN '111111'

ELSE '' END;

IF (LENGTH(:binstring) >= 8) THEN

decoded := decoded || CHAR(

TO_INT(SUBSTRING(:binstring, 1, 1)) * 128 +

TO_INT(SUBSTRING(:binstring, 2, 1)) * 64 +

TO_INT(SUBSTRING(:binstring, 3, 1)) * 32 +

TO_INT(SUBSTRING(:binstring, 4, 1)) * 16 +

TO_INT(SUBSTRING(:binstring, 5, 1)) * 8 +

TO_INT(SUBSTRING(:binstring, 6, 1)) * 4 +

TO_INT(SUBSTRING(:binstring, 7, 1)) * 2 +

TO_INT(SUBSTRING(:binstring, 8, 1)) * 1);

binstring := SUBSTRING(:binstring, 9);

END IF;

END FOR;

END;