CODEWITHSUNDEEP
sqlcastingdb2blobvarchar
S K
S K

Reputation: 33

DB2 cast partial blob to varchar

I have a db2 table with a blob column which contains gzipped xml data. Now I'd like to get approximately 20 characters from the beginning of the xml. I tried to cast the blob to a varchar, which works fine as long as the whole blob can be cast to a varchar. If it is too long, then i will get an error and the sql request breaks with following error: SQL Error [22001]: Value "..." is too long.. SQLCODE=-433, SQLSTATE=22001, DRIVER=4.14.88

Is there any chance to cast only the first part of the blob?

What i'd like to achieve is, to get an id from the xml, which than can be used as a part of another sql request.

My sql so far:

SELECT SUBSTRING(CAST(DATA AS VARCHAR(17)),10,8,OCTETS)
FROM table
WHERE TRANSACTION_ID = '123';

Thank you!

Upvotes: 1

Views: 7393

Answers (1)

S K
S K

Reputation: 33

As dnoeth said in his comment, the answer is simply to substr the blob itself before casting it.
So the sql looks like this:
SELECT CAST(SUBSTR(DATA,10,8) AS VARCHAR(8)) FROM table WHERE TRANSACTION_ID = '123';

Upvotes: 1

Related Questions