CODEWITHSUNDEEP
sqldb2
user9965636
user9965636

Reputation: 13

Base64 decode in db2 sql

Is it possible to decode a Base64 encoded string in DB2 database?

I was able to decode the string in SQL Server by casting to XML.

I am running db2 in Linux server

Upvotes: 0

Views: 1138

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12339

-- encoding 
values regexp_replace (xmlserialize (xmlelement (name "a", blob (X'1122334455')) as varchar (20)), '^<a>(.*)</a>$', '$1')
1
ESIzRFU= 
-- decoding (hex function use is just to get a string representation of a binary value)
values hex (xmlcast (xmltext ('ESIzRFU=') as blob (20)))
1
1122334455

Upvotes: 0

Dean Kuga
Dean Kuga

Reputation: 12119

z/OS:

BASE64ENCODE and BASE64DECODE Last Updated: 2022-08-23

The BASE64ENCODE and BASE64DECODE helper REST functions complete Base64 encoding or decoding of the provided text.

Tip: The sample HTTP user-defined functions are intended to be used within Db2 SQL applications to access remote non-Db2 REST-based services through SQL statements. Do not confuse them with Db2 native REST services, which supports using a REST-based interface to interact with Db2 data from web, mobile, and cloud applications.

enter image description here

The schema is DB2XML.

text

Specifies the text to encode or decode. For BASE64ENCODE, this argument is provided as a VARCHAR(2732) value and the function returns a Base64-encoded string. For BASE64DECODE, this argument is provided as a Base64-encoded VARCHAR(4096) value and the function returns the data as binary.

IBMi:

BASE64DECODE scalar function Last Updated: 2022-05-03

The BASE64DECODE function returns a character string that has been Base64 decoded. Base64 encoding is widely used to represent binary data as a string.

enter image description here

The schema is SYSTOOLS.

character-string

A character string in CCSID 1208 that is currently Base64 encoded. The length cannot exceed 4096 characters.

The result of the function is a varying length character for bit data string that contains character-string after being Base64 decoded.

Example

  • Decode a binary string that was originally X'1122334455'. The result is the original value.

    VALUES SYSTOOLS.BASE64DECODE('ESIzRFU=');

Upvotes: 1

Related Questions