Builtin Functions
- Capella Columnar
- reference
This section introduces the builtin SQL++ for Capella columnar functions.
For reference, a categorized list of all of the builtin functions follows. Use the category linked at the end of each list for descriptions and examples of those functions.
Some of the examples in this section assume that you’re using a database called sampleAnalytics and a scope called Commerce.
See Example Data to install the Commerce dataset.
Numeric Functions
abs |
acos |
asin |
atan |
atan2 |
ceil |
cos |
cosh |
degrees |
e |
exp |
floor |
ln |
log |
pi |
power |
radians |
round |
sign |
sin |
sinh |
sqrt |
tan |
tanh |
trunc |
See Numeric Functions.
String Functions
concat |
contains |
ends_with |
initcap (or title) |
length |
lower |
ltrim |
position |
regexp_contains |
regexp_like |
regexp_position |
regexp_replace |
repeat |
replace |
reverse |
rtrim |
split |
starts_with |
substr |
trim |
See String Functions.
Temporal Functions
now_local (clock_local) |
now_millis (clock_millis) |
now_str (clock_str) |
now_tz (clock_tz) |
now_utc (clock_utc) |
date_add_millis |
date_add_str |
date_diff_millis |
date_diff_str |
date_format_str |
date_part_millis |
date_part_str |
date_range_millis |
date_range_str |
date_trunc_millis |
date_trunc_str |
duration_to_str |
millis |
millis_to_str (millis_to_local) |
millis_to_tz (millis_to_zone_name) |
millis_to_utc |
str_to_duration |
str_to_millis |
str_to_utc |
str_to_tz (str_to_zone_name) |
See Temporal Functions.
Object Functions
object_add |
object_concat |
object_length |
object_names |
object_pairs |
object_put |
object_rename |
object_remove |
object_replace |
object_unwrap |
See Object Functions.
Aggregate Functions
array_count |
array_avg |
array_sum |
array_min |
array_max |
array_stddev_samp |
array_stddev_pop |
array_var_samp |
array_var_pop |
array_skewness |
array_kurtosis |
strict_count |
strict_avg |
strict_sum |
strict_min |
strict_max |
strict_stddev_samp |
strict_stddev_pop |
strict_var_samp |
strict_var_pop |
See Aggregate Functions.
Array Functions
array_append |
array_concat |
array_contains |
array_distinct |
array_flatten |
array_ifnull |
array_insert |
array_intersect |
array_length |
array_position |
array_prepend |
array_put |
array_range |
array_remove |
array_repeat |
array_replace |
array_reverse |
array_sort |
array_star |
array_symdiff |
See Array Functions.
Type Functions
is_array |
is_multiset |
is_atomic (is_atom) |
is_Boolean (is_bool) |
is_number (is_num) |
is_object (is_obj) |
is_string (is_str) |
is_null |
is_missing |
is_unknown |
to_array |
to_atomic (to_atom) |
to_boolean (to_bool) |
to_bigint |
to_double |
to_number (to_num) |
to_object (to_obj) |
to_string (to_str) |
typename |
array_infer_schema |
See Type Functions.
Conditional Functions
if_null (ifnull) |
if_missing (ifmissing) |
if_missing_or_null (ifmissingornull, coalesce) |
if_inf (ifinf) |
if_nan (ifnan) |
if_nan_or_inf (ifnanorinf) |
null_if (nullif) |
missing_if (missingif) |
nan_if (nanif) |
posinf_if (posinfif) |
Bitwise Functions
bitand |
bitclear |
bitnot |
bitor |
bitset |
See Bitwise Functions.
Window Functions
cume_dist |
dense_rank |
first_value |
lag |
last_value |
lead |
nth_value |
ntile |
percent_rank |
rank |
See Window Functions.