Search code examples
sqlexcelfirebirdpowerpivot

sql read blob as text

I am trying to output a table field named observation as text where it is stored as blob. The SQL is 

select invoice_id, observation from table1

The output I am getting is as.raw(c(0x31, 0xba, 0x20, 0x50, 0x52, 0x4f, 0x4d, 0x4f, 0x20...

How can I output as text?

Solution

  • If you are saving text in a blob, you should make it a blob sub_type text (aka blob sub_type 1) instead of a blob (aka blob sub_type binary aka blob sub_type 0). Though that is not a guarantee (some Firebird drivers don't distinguish between blob sub types).

    To convert a binary blob to a text blob, use 

    select cast(binblob as blob sub_type text character set utf8) from blobtbl

    or without character set clause:

    select cast(binblob as blob sub_type text) from blobtbl

    Alternatively, you could cast to VARCHAR, but make sure you specify a long enough varchar to hold the entire value, otherwise you'll get a truncation error. For example:

    select cast(binblob as varchar(1000) character set utf8) from blobtbl

    In both cases, the character set clause is optional; it will use the connection character set if left off. Be aware if you use the wrong character set, you might get a transliteration error.