Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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

View solution in original post

2 Replies
kevinpintokpa
Creator II
Creator II

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
Not applicable
Author

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