How to convert BLOB to varchar with SAP HANA database using SQL

sql blob sap varchar hana
Thorsten Niehues picture Thorsten Niehues · Oct 29, 2014 · Viewed 15.1k times · Source

how do I cast a blob to varchar with SAP HANA database using SQL.

(we need the column to be stored in blob - not TEXT - in else HANA automatically creates an index on this column. But we need an index with full-text-search and CORE_EXTRACTION)

The following code

select 
cast("DESCRIPTION" as varchar) "D"
from "DESC"

returns 

Could not execute 'select cast("DESCRIPTION" as varchar) "D" from "DESC"' in 30 ms 168 µs . 
SAP DBTech JDBC: [266]: inconsistent datatype:

Answer

Thorsten Niehues picture Thorsten Niehues · Oct 29, 2014

Solved the problem with this select statement

select cast(BINTOSTR(cast(description as binary)) as varchar) from xing_desc

Related questions

How do I get textual contents from BLOB in Oracle SQL

I am trying to see from an SQL console what is inside an Oracle BLOB. I know it contains a somewhat large body of text and I want to just see the text, but the following query only indicates that …

Read More
dbms_lob.getlength() vs. length() to find blob size in oracle

I'm getting the same results from select length(column_name) from table as select dbms_lob.getlength(column_name) from table However, the answers to this question seem to favor using dbms_lob.getlength(). Is there any benefit to using …

Read More
How to insert a blob into a database using sql server management studio

How can I easily insert a blob into a varbinary(MAX) field? As an example: thing I want to insert is: c:\picture.png the table is mytable the column is mypictureblob the place is recid=1

Read More