Solved: Convert blob (UTF8) i.e. Byte Array data into text... - Qlik Community

Anonymous · ‎2017-04-03

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.

Anonymous · ‎2017-06-15

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.

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
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

View solution in original post

kevinpintokpa · ‎2017-06-15

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.

Anonymous · ‎2017-06-15

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.

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
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

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