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;
If anybody wonders about the reverse procedure, here's a function to encode, too. I'm not exactly happy about the length of it, but I do think that breaking it down further to reduce the lines of code will just make it slower... Again, any improvements are welcome!
By the way, if you are interested in completely native functions, here is a link to the official improvement request: Provide Base64 decode/encode as native database function.
Thanks for voting!