CODEWITHSUNDEEP
sqldb2oracle-sqldeveloper
Prudhvi Chandra
Prudhvi Chandra

Reputation: 21

how to use BASE64DECODE function in DB2

I have a column in table (DB2 database) which contains base64 binary encoded data. I'm trying to read the data from sql developer using BASE64DECODE function.

I have tried below ways, but none of them seems working

Select BASE64DECODE(COLUMN_NAME) from ENCRYPTED_DATA

Select SYSTOOLS.BASE64DECODE(COLUMN_NAME) from ENCRYPTED_DATA

Upvotes: 1

Views: 4665

Answers (1)

Mark Barinstein
Mark Barinstein

Reputation: 12339

If your Db2 platform is Linux, Unix and Windows, then there is no such a standard function. You may create and use the following:

create or replace function BASE64ENCODE(b blob(1048576))
returns clob(1398102)
contains sql
deterministic 
no external action
return xmlcast(xmlquery('$d/a' passing xmldocument(xmlelement(name "a", b)) as "d") as clob(1398102))
;

create or replace function BASE64DECODE(c clob(1398102))
returns blob(1048576)
contains sql
deterministic 
no external action
return xmlcast(xmlquery('$d/a' passing xmldocument(xmlelement(name "a", c)) as "d") as blob(1048576))
;

values hex(cast(BASE64DECODE(BASE64ENCODE(blob(x'1122334455'))) as varchar(5) for bit data));

1
----------
1122334455

Upvotes: 2

Related Questions