Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

branawat
New Contributor II

Convert blob (UTF8) i.e. Byte Array data into text for a column in a table.

I need to convert blob (UTF8) i.e. Byte Array data into text for a column in my table.

One of my reports need to get data from an DB2 database, which i am able to successfully connect.  But the column i am looking for is stored as BLOB though the content is string (text). Qlik read's the data but is of no use as it is displayed as numbers. For example the text 'unable.. looks like '54686973206...

I was looking for a way if i can convert/cast the column data into text in Qlik.

1 Solution

Accepted Solutions
branawat
New Contributor II

Re: Convert blob (UTF8) i.e. Byte Array data into text for a column in a table.

There is no solution in Qlik sense.

You have to modify your data source. I was lucky enough to get it modified.

There is a DB2 solution where you can convert blob data into varchar i.e text.

  1. CREATE FUNCTION in DB2:

    CREATE FUNCTION unhex(in VARCHAR(32000) FOR BIT DATA) RETURNS VARCHAR(32000) LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC RETURN in; END

  2. Run SELECT:select UNHEX( CAST(BLOBCOLUMN as VARCHAR(32000) FOR BIT DATA)) from TABLE where id = 100;

Link to the post: sql - Convert HEX value to CHAR on DB2 - Stack Overflow

2 Replies
kevinpintokpa
Contributor II

Re: Convert blob (UTF8) i.e. Byte Array data into text for a column in a table.

Hi Bakul,

Did you find an answer to this question?  I have the same issue - a DB2 Blob field that looks like a long string of numbers.

branawat
New Contributor II

Re: Convert blob (UTF8) i.e. Byte Array data into text for a column in a table.

There is no solution in Qlik sense.

You have to modify your data source. I was lucky enough to get it modified.

There is a DB2 solution where you can convert blob data into varchar i.e text.

  1. CREATE FUNCTION in DB2:

    CREATE FUNCTION unhex(in VARCHAR(32000) FOR BIT DATA) RETURNS VARCHAR(32000) LANGUAGE SQL CONTAINS SQL DETERMINISTIC NO EXTERNAL ACTION BEGIN ATOMIC RETURN in; END

  2. Run SELECT:select UNHEX( CAST(BLOBCOLUMN as VARCHAR(32000) FOR BIT DATA)) from TABLE where id = 100;

Link to the post: sql - Convert HEX value to CHAR on DB2 - Stack Overflow