Conversion Functions

cast Conversion Function

HetuEngine implicitly converts numeric and character values to the correct type. HetuEngine does not convert between character and numeric types. For example, if a query expects a value of the varchar type, HetuEngine does not automatically convert a value of the bigint type to a value of the varchar type.

Values can be explicitly converted to the specified type, if necessary.

• cast(value AS type) → type

  Explicitly converts the type of a value. You can convert a value of the varchar type to the numeric type, or vice versa.

  select cast('186' as int );
  select cast(186 as varchar);

• try_cast(value AS type) → type

  It is similar to cast(). The difference is that null is returned if the conversion fails.

  select try_cast(1860 as tinyint);
   _col0 
  -------
    NULL 
  (1 row)

When a number overflows or a null value is converted, null is returned. However, when the conversion fails, an error is reported.

Example: select try_cast(186 as date);

Cannot cast integer to date

Format

• format(format, args...) → varchar

  Description: Formats a string in the format specified by the format string and returns the formatted string.

  SELECT format('%s%%',123);-- '123%'
  SELECT format('%.5f',pi());-- '3.14159'
  SELECT format('%03d',8);-- '008'
  SELECT format('%,.2f',1234567.89);-- '1,234,567.89'
  SELECT format('%-7s,%7s','hello','world');-- 'hello  ,  world'
  SELECT format('%2$s %3$s %1$s','a','b','c');-- 'b c a'
  SELECT format('%1$tA, %1$tB %1$te, %1$tY',date'2006-07-04');-- 'Tuesday, July 4, 2006

Data Size

The parse_presto_data_size function supports the following units:

parse_presto_data_size(string) → decimal(38)

Convert formatted values with a unit to a number. The value can be a decimal.

SELECT parse_presto_data_size('1B'); -- 1
SELECT parse_presto_data_size('1kB'); -- 1024
SELECT parse_presto_data_size('1MB'); -- 1048576
SELECT parse_presto_data_size('2.3MB'); -- 2411724

Others

typeof(expr) → varchar

Returns the data type name of an expression.

SELECT typeof(123);-- integer
SELECT typeof('cat');-- varchar(3)
SELECT typeof(cos(2)+1.5);-- double