Please use the form below to provide your feedback. Because your feedback is valuable to us, the information you submit in this form is recorded in our issue tracking system (JIRA), which is publicly available. You can track the status of your feedback using the ticket number displayed in the dialog once you submit the form.

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.

    Comparison Functions

    greatest

    least

    See Comparison 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)

    See Conditional Functions.

    Environment and Identifier Functions

    meta

    uuid

    See Environment and Identifier Functions.

    JSON Functions

    decode_json

    encode_json

    encoded_size

    See JSON Functions.

    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.