How to convert BLOB to varchar with SAP HANA database using SQL
Solution 1
Solved the problem with this select statement
select cast(BINTOSTR(cast(description as binary)) as varchar) from xing_desc
Solution 2
Casting to VARCHAR
usually is done by
SELECT TO_ALPHANUM(col) FROM ...
Thorsten Niehues
Working for Sybit & Peers, specializing in SAP HANA Worked as Head of IT for ContiTech North America Worked for many other companies (e.g. Bosch, T-Systems, IBM) programming in many languages (Java, Objective-C, PHP, JavaScript, etc.) and worked a lot with many databases (DB2, Oracle, MySQL, etc.) Studied computer science (Master) at HTWG Konstanz Studied computer science (Bachelor) at HFT Stuttgart Ongoing project: FindClimb an app which helps climbers to find their way to a climbing spot Technologies: iOS, Android, Google-Maps, Google-Shopping, HTML, Objective-C, Java, JS, VB, VBA, Perl, Shell, XML, XSD, Xcode, Eclipse, Linux (Ubuntu/Solaris/Suse/RedHat), OS X (10.8 Mountain Lion / 10.10 Yosemite), Windows (XP/Windows Server 2008/Vista/Windows7/Windows8/Windows10)
Updated on June 05, 2022Comments
-
Thorsten Niehues almost 2 years
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:
-
anand over 3 yearsThis 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"