Cast

BOOLEAN

BOOLEAN(string)

Returns TRUE on following string values: T, t, Y, y, 1, YES, yes, TRUE, true.

Returns NULL if string argument evaluates to NULL.

Returns FALSE on any other string value.

output type:BOOLEAN

Example:

1
dual | select(bval:BOOLEAN('T'));
bval
true

BOOLEAN(numeric_expr)

numeric_expr value 0 is converted to false, other values to true.

Returns NULL if numeric_expr evaluates to NULL.

output type:BOOLEAN

Example:

1
dual(3) | select(bval:BOOLEAN(i));
bval
false
true
true

BYTES

BYTES(string)

Converts string argument to BYTES. Returns NULL if string evaluates to NULL.

output type:BYTES

Example:

1
dual | select(b:BYTES('01'));
b
[48,49]

BYTES(numeric_array)

Converts numeric_array values to BYTES. Returns NULL if numeric_array evaluates to NULL.

output type:BYTES

Example:

1
dual | select(b:BYTES([1023,1024,1]));
b
[-1,0,1]

DOUBLE

DOUBLE(numeric_expr)

Converts numeric_expr to DOUBLE. Returns NULL if numeric_expr evaluates to NULL.

output type:DOUBLE

Example

1
dual | select(d:DOUBLE(2 * 300));
d
600.0

DOUBLE(string)

Converts string argument to DOUBLE. Returns NULL if string evaluates to NULL.

output type:DOUBLE

Example

1
    dual | select(d:DOUBLE("-4"));
d
-4.0

FLOAT

FLOAT(numeric_expr)

Converts numeric_expr to FLOAT. Returns NULL if numeric_expr evaluates to NULL.

output type:FLOAT

Example

1
dual | select(f:FLOAT(2 * 300));
f
600.0

FLOAT(string)

Converts string argument to FLOAT. Returns NULL if string evaluates to NULL.

output type:FLOAT

Example

1
dual | select(f:FLOAT("-4"));
f
-4.0

GEOPOINT

GEOPOINT, GEO(ipaddr)

Converts ipaddr argument to GEOPOINT. Returns NULL if ipaddr evaluates to NULL.

output type:GEOPOINT

Example

1
dual | select(geopoint:GEO(8.8.8.8));
geopoint
37.751,-97.822

GEOPOINT | GEO(double1, double2)

Converts geographical coordinates to GEOPOINT. Returns NULL if double1 and double2 evaluates to NULL.

output type:GEOPOINT

Example

1
dual | select(geopoint:GEO(59.3984, 24.6563));
geopoint
59.3984,24.6563

INTEGER

INTEGER, INT(numeric_expr)

Converts numeric_expr to INTEGER. Returns NULL if numeric_expr evaluates to NULL.

output type:INTEGER

Example

1
dual | select(i:INT(2.498));
i
2

INTEGER, INT(string)

Converts string argument to INTEGER. Returns NULL if string evaluates to NULL.

output type:INTEGER

Example

1
dual | select(i:INT("-4"));
i
-4

INTEGER, INT(timestamp)

Converts timestamp argument to INTEGER. Returns NULL if timestamp evaluates to NULL.

output type:INTEGER

Example

1
dual | select(i:INT(TIMESTAMP("2019-03-14 22:41:55.000 +0000")));
i
1552603315

INTEGER, INT(timestamp_nano)

Converts timestamp_nano argument. Returns NULL if timestamp_nano evaluates to NULL.

Converts timestamp_nano argument to INTEGER. Returns NULL if timestamp_nano evaluates to NULL.

output type:INTEGER

Example

1
dual | select(i:INT(TIMESTAMP_NANO("2019-03-14 22:41:55.000 +0000")));
i
1552603315

IPADDR

IPADDR(numeric_expr), IPV4(numeric_expr)

Converts numeric_expr arg to IPADDR. Returns NULL if numeric_expr evaluates to NULL.

output type:IPADDR

Example

1
dual | select(ip:IPADDR(-1062731520));
ip
192.168.1.0

IPADDR(hi_64_bit_num, low_64_bit_num), IPV6(IPADDR(hi_64_bit_num, low_64_bit_num))

Converts 128-bit numeric argument to IPADDR. Returns NULL if arguments evaluate to NULL.

output type:IPADDR

Example

1
dual | select(ip:IPADDR(1,1));
ip
::1:0:0:0:1

IPADDR(string), IPV4(string), IPV6(string)

Converts string arg to IPADDR. Returns NULL if string evaluates to NULL.

output type:IPADDR

Example

1
dual | select(ip:IPADDR("192.168.1.0"));
ip
192.168.1.0

IPSOCKET

IPSOCKET(ipaddr, port), IPV4SOCKET(ipaddr, port), IPV6SOCKET(ipaddr, port)

Converts ipaddr and numeric port arguments to IPSOCKET. Returns NULL if arguments evaluate to NULL.

output type:IPSOCKET

Example

1
dual | select(ipsocket:IPSOCKET(192.168.1.0,443));
ipsocket
192.1681.0:443

IPSOCKET(string), IPV4SOCKET(string), IPV6SOCKET(string)

Converts string representing IPv4 address followed by a colon ‘:’ and a port number to IPSOCKET.

Returns NULL if argument evaluates to NULL.

output type:IPSOCKET

Example

1
dual | select(ipsocket:IPSOCKET("192.168.1.0:443"));
ipsocket
192.1681.0:443

IPNET

IPNET(ipaddr, mask), IPV4NET(ipaddr, mask)

Converts ipaddr and numeric mask arguments to IPNET. Returns NULL if arguments evaluate to NULL.

output type:IPNET

Example

1
dual | select(ipnet:IPNET(192.168.1.134, 24));
ipnet
192.168.1.134/24

IPNET(string), IPV4NET(string)

Converts CIDR notation string argument to IPNET. Returns NULL if string argument evaluates to NULL.

output type:IPNET

Example

1
dual | select(ipnet:IPNET("2a00:1450:4010:c05::69/8"));
ipnet
2a00:1450:4010:c05::69/8

LONG

LONG(numeric_expr)

Converts numeric_expr to LONG. Returns NULL if numeric_expr evaluates to NULL.

output type:LONG

Example

1
dual | select(l:LONG(2.498));
l
2

LONG(string)

Converts string argument to LONG. Returns NULL if string evaluates to NULL.

output type:LONG

Example

1
dual | select(l:LONG("-4"));
l
-4

MACADDR

MACADDR(numeric_expr), MAC_ADDR(numeric_expr)

Converts numeric_expr to MACADDR. Returns NULL if numeric_expr evaluates to NULL.

output type:MACADDR

Example

1
dual | select(macaddr:MACADDR(3 + 1));
macaddr
00-00-00-00-00-04

MACADDR(string), MAC_ADDR(string)

Converts string, representing MAC address in the form of 12 hexadecimal numbers optionally separated by a hyphen “-“, colon “:” or dot “.”, to MACADDR.

Returns NULL if string evaluates to NULL.

output type:MACADDR

Example

1
dual | select(macaddr:MACADDR('00-00-00-00-00-04'));
macaddr
00-00-00-00-00-04

STRING

STRING(expr)

Converts expr returning any type to STRING. Returns NULL if expr evaluates to NULL.

output type:STRING

Example

1
dual(3) | select(int_str:STRING(i), float_str:STRING(f), ip_str:STRING(ip));
int_str float_str ip_str
0 0.0 0.0.0.0
1 1.0 0.0.0.1
2 2.0 0.0.0.2

TIMESTAMP

TIMESTAMP(integer), T(integer)

Converts integer seconds elapsed from Unix epoch to TIMESTAMP.

Returns NULL if integer evaluates to NULL.

output type:TIMESTAMP

Example

1
dual | select(TIMESTAMP(1552603315));
timestamp
2019-03-14 22:41:55.000 +0000

TIMESTAMP(long), T(long)

Converts long milliseconds elapsed from Unix epoch to TIMESTAMP.

Returns NULL if long evaluates to NULL.

output type:TIMESTAMP

Example

1
dual | select(T(1552603315l*1000+333));
timestamp
2019-03-14 22:41:55.333 +0000

TIMESTAMP(year, month, day), T(year, month, day)

Converts date specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP

Example

1
dual | select(TIMESTAMP(2019,03,14));
timestamp
2019-03-14 00:00:00.000 +0000

TIMESTAMP(year, month, day, hour), T(year, month, day, hour)

Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP

Example

1
dual | select(TIMESTAMP(2019,03,14,22));
timestamp
2019-03-14 22:00:00.000 +0000

TIMESTAMP(year, month, day, hour, minutes), T(year, month, day, hour, minutes)

Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP

Example

1
dual | select(TIMESTAMP(2019,03,14,22,41));
timestamp
2019-03-14 22:41:00.000 +0000

TIMESTAMP(year, month, day, hour, minutes, seconds), T(year, month, day, hour, minutes, seconds)

Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP

Example

1
dual | select(TIMESTAMP(2019,03,14,22,41,55));
timestamp
2019-03-14 22:41:55.000 +0000

TIMESTAMP(year, month, day, hour, minutes, seconds, milliseconds)

T(year, month, day, hour, minutes, seconds, milliseconds)

Converts date and time specified by integer arguments to TIMESTAMP. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP

Example

1
dual | select(TIMESTAMP(2019,03,14,22,41,55,333));
timestamp
2019-03-14 22:41:55.333 +0000

TIMESTAMP(string), T(string)

Converts following format string args to to TIMESTAMP:

  • “yyyy-MM-dd HH:mm:ss”
  • “yyyy-MM-dd HH:mm:ss Z”
  • “yyyy-MM-dd HH:mm:ss.SSS”
  • “yyyy-MM-dd HH:mm:ss.SSS Z”

Returns NULL if argument string evaluates to NULL.

output type:TIMESTAMP

Example

1
    dual | select(TIMESTAMP("2019-03-14 22:41:55.333 GMT"));
timestamp
2019-03-14 22:41:55.333 +0000

TIMESTAMP_NANO

TIMESTAMP_NANO, TNANO(long)

Converts long nanoseconds elapsed from Unix expoch to TIMESTAMP_NANO.

Returns NULL if long argument evaluates to NULL.

output type:TIMESTAMP_NANO

Example

1
dual | select(TIMESTAMP_NANO(155260331535353535));
timestamp_nano
1974-12-02 22:52:11.535353535

TIMESTAMP_NANO, TNANO(year, month, day)

Converts date specified by integer arguments to TIMESTAMP_NANO. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP_NANO

Example

1
    dual | select(TIMESTAMP_NANO(2019,03,14));
timestamp_nano
2019-03-14 00:00:00.000000000

TIMESTAMP_NANO, TNANO(year, month, day, hour)

Converts date and time specified by integer arguments to TIMESTAMP_NANO. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP_NANO

Example

1
dual | select(TIMESTAMP_NANO(2019,03,14,22));
timestamp_nano
2019-03-14 22:00:00.000000000

TIMESTAMP_NANO, TNANO(year, month, day, hour, minute)

Converts date and time specified by integer arguments to TIMESTAMP_NANO. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP_NANO

Example

1
    dual | select(TIMESTAMP_NANO(2019,03,14,22,52));
timestamp_nano
2019-03-14 22:52:00.000000000

TIMESTAMP_NANO, TNANO(year, month, day, hour, minute, second)

Converts date and time specified by integer arguments to TIMESTAMP_NANO. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP_NANO

Example

1
dual | select(TIMESTAMP_NANO(2019,03,14,22,52,11));
timestamp_nano
2019-03-14 22:52:11.000000000

TIMESTAMP_NANO, TNANO(year, month, day, hour, minute, second, millisecond)

Converts date and time specified by integer arguments to TIMESTAMP_NANO. Returns NULL if arguments evaluate to NULL.

output type:TIMESTAMP_NANO

Example

1
dual | select(TIMESTAMP_NANO(2019,03,14,22,52,11,535));
timestamp_nano
2019-03-14 22:52:11.535000000

TIMESTAMP_NANO, TNANO(string)

Converts following format string args to to TIMESTAMP_NANO:

  • “yyyy-MM-dd HH:mm:ss”
  • “yyyy-MM-dd HH:mm:ss Z”
  • “yyyy-MM-dd HH:mm:ss.SSS”
  • “yyyy-MM-dd HH:mm:ss.SSS Z”

Returns NULL if argument string evaluates to NULL.

output type:TIMESTAMP_NANO

Example

1
dual | select(TIMESTAMP_NANO("2019-03-14 22:52:11.535 GMT"));
timestamp_nano
2019-03-14 22:52:11.535000000

TO_NULL

TO_NULL(expr)

Returns NULL value.

output type:the type of supplied argument

Example

1
dual | select(n_i:TO_NULL(i), n_ip:TO_NULL(ip), n_time:TO_NULL(t), n_str:TO_NULL(s));

Result details (double click on resultset):

name value type
n_i NULL INTEGER
n_ip NULL IPADDR
n_time NULL TIMESTAMP
n_str NULL STRING

TUPLE

TUPLE(variant_object)

Converts VARIANT_OBJECT argument to TUPLE.

output type:TUPLE

Example

1
2
3
dual
| select(vo:VARIANT_OBJECT({a:1, b:"text", c:{foo:2.3}}))
| select(vo, TYPE(vo))
vo type
{“a”:1,”b”:text,”c”:{“foo”:2.3}} VARIANT_OBJECT

TUPLE(expr, …)

Converts one or more expressions returning any data type to TUPLE

output type:TUPLE

Example

1
dual | select(t:TUPLE(1,2.0,"foo")) | select(t, TYPE(t));
t type
{f_0=1 f_1=2.0 f_2=”foo”} TUPLE

VARIANT

VARIANT(expr)

Converts primitive type expr to VARIANT. Returns NULL if expr evaluates to NULL.

output type:VARIANT

Example

1
dual | select(var:VARIANT(s));

Result details (double click on resultset row):

name value type
var 0ho0 VARIANT<STRING>

VARIANT_ARRAY

VARIANT_ARRAY(arg)

Converts ARRAY or primitive type arg to VARIANT_ARRAY

output type:VARIANT_ARRAY

Example

1
dual | select(res:VARIANT_ARRAY([1,2,3])) | select(res, TYPE(res));
res type
[1,2,3] VARIANT_ARRAY

VARIANT_OBJECT

VARIANT_OBJECT(expr)

Converts TUPLE, VARIANT or primitive type expr to VARIANT_OBJECT.

output type:VARIANT_OBJECT

Example

1
dual | select(vo_tuple:VARIANT_OBJECT({a:1, b:2})) | select(vo_tuple, TYPE(vo_tuple));
vo_tuple type
{“a”:1,”b”:2} VARIANT_OBJECT