3

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:

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113

2 Answers2

7

Solved the problem with this select statement

select cast(BINTOSTR(cast(description as binary)) as varchar) from xing_desc
Thorsten Niehues
  • 13,712
  • 22
  • 78
  • 113
2

Casting to VARCHAR usually is done by 

SELECT TO_ALPHANUM(col) FROM ...
Benvorth
  • 7,416
  • 8
  • 49
  • 70
  • This will only work if the length is <= 127 chars.Otherwise you will get the error: SQL Error [384] [HY000]: SAP DBTech JDBC: [384]: string is too long: Alphanum length should be smaller than or equal to 127 at "to_alphanum" – anand Aug 12 '20 at 06:45