[SAP HANA Academy] SAP HANA SPS11 - SQL Functions: BinToHex & BinToNHex Functions

[Former Member]

In a tutorial video the SAP HANA Academy's jamie.wiseman examines two binary to hexadecimal conversion SQL Functions. Jamie details how to use the BinToHex () and the BinToNHex() functions. This pair of functions are new to SAP HANA SPS11. Watch Jamie's video below.

The difference between the two new SQL functions is the output type. The conversion of BinToHex is a VarChar output while the conversion of BinToNHex is a NVarChar output.

Jamie enters the syntax shown below and displayed at the bottom of this post into a SQL console in SAP HANA Studio.

Both functions require a single parameter (input value) and produce a hexadecimal value as the output. Running the syntax returns identical results for the two queries, except that the BinToNHex has a NVarChar output.

So the decision to use BinToHex or BinToNHex will largely depend on what your application is expecting.

Note that both the BinToHex and BinToNHex functions will automatically convert any input to binary. Therefore you won't need to use a To_BINARY conversion. A To_BINARY conversion is currently displayed in the second column of the result tables highlighted above.

For more tutorials videos on What's New with SAP HANA SPS11 please check out this playlist.

SAP HANA Academy - Over 1,300 free tutorials videos on SAP HANA, SAP Analytics and the SAP HANA Cloud Platform.

Follow us on Twitter @saphanaacademy and connect with us on LinkedIn to keep apprised of our latest free tutorials.

Tutorial's Syntax:

SELECT

BINTOHEX('ABC'),

BINTOHEX(TO_BINARY('ABC'))

FROM DUMMY;

-- Converts a binary value to a hexadecimal value as a VARCHAR data type.

-- The input value is converted to a binary value first if it is not a binary value.

--results:

--414243, 414243

SELECT

BINTONHEX('ABC'),

BINTONHEX(TO_BINARY('ABC'))

FROM DUMMY; 

-- Converts a binary value to a hexadecimal value as an NVARCHAR data type.

-- The input value is converted to a binary value first if it is not a binary value.

--results:

--414243, 414243