SAP HANA Equivalent logic to Try_Cast

Question

In SQL Server you have a function called Try_Cast. https://learn.microsoft.com/en-us/sql/t-sql/functions/try-cast-transact-sql?view=sql-server-2017

I like that you can put this into a Case statement:

SELECT   
CASE WHEN TRY_CAST('test' AS float) IS NULL   
THEN 'Cast failed'  
ELSE 'Cast succeeded'  
END AS Result;  

I need to have this exact same logic in SAP HANA.

Any ideas?

Answer 1

HANA does not have an equivalent so far to my knowledge. If you have specific casts (e.g. from nvarchar to float) you can create your own scalar function to handle the "try cast" using an exit handler, which checks on the relevant data type conversion sql error code.

For nvarchar to float this function can look like following:

FUNCTION "try_cast_nvarchar_2_float" ( i_value nvarchar(5000) ) 
RETURNS e_result float
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER AS 
BEGIN
  DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 339
    e_result = null;
  
  e_result = cast(i_value as float);
END;

This function than can be used in plain sql; with the case expression too like in your example:

SELECT 
  "try_cast_nvarchar_2_float"('10.34') AS casted_value,
  CASE
    WHEN "try_cast_nvarchar_2_float"(:lv_value) IS NULL
    THEN 'Cast failed'
    ELSE 'Cast succeeded'
  END AS cast_result
FROM dummy;