Documentation

    Functions

    Clean text, calculate values and write conditions. Use these expressions in Formula to create columns or in Filter to choose rows.

    All functions

    347 functions

    Text

    ascii

    Return the Unicode number of the first character in text.

    bin

    Converts the string to binary representation.

    bit_length

    Number of bits in a string.

    chr

    Returns a character which corresponds to the ASCII code value or Unicode code point.

    concat

    Join text values or lists into one value. Missing values are skipped, while the || operator returns a missing value if any input is missing.

    concat_ws

    Join text values with a separator between them. Missing values are skipped.

    contains

    Check whether text contains a search string. It also checks a list for an item, a map for a key and an unnamed struct for a value.

    damerau_levenshtein

    Count the edits needed to turn one string into another, like levenshtein, but swapping two neighboring characters counts as one edit. Uppercase and lowercase letters count as different.

    format

    Fill {} placeholders in text with values, as in format('{} and {}', 'a', 1).

    format_bytes

    Convert a byte count to a readable size using units based on powers of 2, such as KiB, MiB and GiB. Also available as pg_size_pretty.

    from_base64

    Decode base64 text into binary data.

    hamming

    Calculate the Hamming distance between two strings: the number of positions where their characters differ. Both strings must have the same length, and uppercase and lowercase letters count as different.

    hash

    Calculate a numeric hash of one or more values. It isn't a cryptographic hash, so don't use it for security.

    hex

    Convert a value to hexadecimal text.

    ilike_escape

    Check whether text matches a LIKE pattern, ignoring uppercase and lowercase. The escape character lets the pattern search for a literal % or _.

    instr

    Find where a search string first appears in text, counting from 1. Returns 0 if there is no match.

    jaccard

    Score how similar two strings are by the characters they share, from 0 to 1. Uppercase and lowercase letters count as different.

    jaro_similarity

    The Jaro similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.

    jaro_winkler_similarity

    The Jaro-Winkler similarity between two strings. Characters of different cases (e.g., a and A) are considered different. Returns a number between 0 and 1. For similarity < score_cutoff, 0 is returned instead. score_cutoff defaults to 0.

    left

    Return the first characters of text, up to the given count.

    left_grapheme

    Extracts the left-most count grapheme clusters.

    length

    Count the characters in text. For a list, count its items.

    length_grapheme

    Number of grapheme clusters in string.

    levenshtein

    Count the fewest character insertions, deletions and substitutions needed to turn one string into another. Uppercase and lowercase letters count as different.

    like_escape

    Check whether text matches a LIKE pattern, treating uppercase and lowercase as different. The escape character lets the pattern search for a literal % or _.

    lower

    Convert text to lowercase.

    lpad

    Add a character to the left of text until it reaches a given length. Longer text is cut from the right to that length.

    ltrim

    Remove spaces from the start of text. Pass a second argument to remove those characters instead.

    md5

    Calculate the MD5 hash of text or binary data, returned as hexadecimal text.

    md5_number

    Return the MD5 hash of text or binary data as a UHUGEINT number.

    md5_number_lower

    Read the lower 64 bits of an MD5 digest as an unsigned integer.

    md5_number_upper

    Read the upper 64 bits of an MD5 digest as an unsigned integer.

    nfc_normalize

    Converts string to Unicode NFC normalized string. Useful for comparisons and ordering if text data is mixed between NFC normalized and not.

    not_ilike_escape

    Check whether text doesn't match a LIKE pattern, ignoring uppercase and lowercase. The escape character lets the pattern search for a literal % or _.

    not_like_escape

    Check whether text doesn't match a LIKE pattern, treating uppercase and lowercase as different. The escape character lets the pattern search for a literal % or _.

    parse_dirname

    Return the top-level directory name from a path. The optional separator can be system, both_slash (default), forward_slash or backslash.

    parse_dirpath

    Return a path up to its last slash, such as a/b for a/b/c.txt. The optional separator can be system, both_slash (default), forward_slash or backslash.

    parse_filename

    Return the last component of a path, such as c.txt for a/b/c.txt. Set trim_extension to true to remove the file extension. The optional separator can be system, both_slash (default), forward_slash or backslash.

    parse_path

    Return the directories and filename in a path as a list. The optional separator can be system, both_slash (default), forward_slash or backslash.

    position

    Find where a search string first appears in text, counting from 1. Returns 0 if there is no match.

    prefix

    Check whether text begins with a given string.

    printf

    Build text from a template with placeholders, such as %s for text and %d for whole numbers.

    repeat

    Repeat text, binary data or a list a given number of times.

    replace

    Replace every occurrence of one piece of text with another.

    reverse

    Reverse the characters in text.

    right

    Return the last characters of text, up to the given count.

    right_grapheme

    Extracts the right-most count grapheme clusters.

    rpad

    Add a character to the right of text until it reaches a given length. Longer text is cut from the right to that length.

    rtrim

    Remove spaces from the end of text. Pass a second argument to remove those characters instead.

    sha1

    Returns a VARCHAR with the SHA-1 hash of the value.

    sha256

    Calculate the SHA-256 hash of text or binary data, returned as hexadecimal text.

    split_part

    Split text at a separator and retrieve one part by its position.

    starts_with

    Check whether text begins with a given string.

    string_split

    Split text at a separator and return the parts as a list.

    strip_accents

    Remove accents from text, so ü becomes u.

    strlen

    Count the bytes in text. Accented letters and emoji take more than one byte each.

    substring

    Extract part of text, starting at a character position where 1 is the first character. Without a length, it continues to the end of the text.

    substring_grapheme

    Extracts substring starting from grapheme clusters start up to the end of the string. If optional argument length is set, extracts a substring of length grapheme clusters instead. Note that a start value of 1 refers to the first character of the string.

    suffix

    Check whether text ends with a given string.

    to_base

    Converts number to a string in the given base radix, optionally padding with leading zeros to min_length.

    to_base64

    Encode binary data as base64 text.

    translate

    Swap individual characters: each character in the second argument becomes the character at the same position in the third. Characters with no counterpart are removed.

    trim

    Remove spaces from both ends of text. Pass a second argument to remove those characters instead.

    unbin

    Converts a value from binary representation to a blob.

    unhex

    Converts a value from hexadecimal representation to a blob.

    unicode

    Returns an INTEGER representing the unicode codepoint of the first character in the string.

    upper

    Standardize text in uppercase before comparing labels or displaying codes.

    url_decode

    Turn percent codes in a URL back into the original characters.

    url_encode

    Make text safe for a URL by replacing spaces and special characters with percent codes.

    Numbers

    abs

    Return the absolute value of a number, removing any minus sign.

    acos

    Computes the arccosine of x.

    acosh

    Computes the inverse hyperbolic cos of x.

    add

    Add two values.

    asin

    Computes the arcsine of x.

    asinh

    Computes the inverse hyperbolic sin of x.

    atan

    Computes the arctangent of x.

    atan2

    Computes the arctangent (y, x).

    atanh

    Computes the inverse hyperbolic tan of x.

    bit_count

    Returns the number of bits that are set.

    cbrt

    Calculate the cube root of a number.

    ceil

    Round a number up to the nearest whole number.

    ceiling

    Round a number up to the nearest whole number.

    cos

    Computes the cos of x.

    cot

    Computes the cotangent of x.

    degrees

    Convert an angle from radians to degrees.

    divide

    Divide whole numbers and discard the fractional part.

    even

    Round a number away from zero to the next even whole number.

    exp

    Raise e, about 2.718, to a given power.

    factorial

    Multiply a whole number by every whole number below it, down to 1.

    fdiv

    Divide two numbers and round the quotient down to a whole number, stored as DOUBLE.

    floor

    Round a number down to the nearest whole number.

    fmod

    Find the remainder using division rounded down toward negative infinity.

    gamma

    Interpolation of (x-1) factorial (so decimal inputs are allowed).

    gcd

    Find the largest whole number that divides two numbers exactly.

    greatest

    Return the largest of its arguments, ignoring missing values. Text is compared by character, so lowercase letters count as larger than uppercase letters.

    greatest_common_divisor

    Find the largest whole number that divides two numbers exactly.

    isfinite

    Check whether a value is finite, meaning neither infinite nor NaN.

    isinf

    Check whether a value is infinite.

    isnan

    Check whether a number is NaN, the special "not a number" value.

    lcm

    Find the smallest whole number that two numbers both divide into exactly.

    least

    Return the smallest of its arguments, ignoring missing values. Text is compared by character, so uppercase letters count as smaller than lowercase letters.

    least_common_multiple

    Find the smallest whole number that two numbers both divide into exactly.

    lgamma

    Computes the log of the gamma function.

    ln

    Calculate the natural logarithm (base e) of a number.

    log

    Calculate a logarithm. With one argument it uses base 10, and with two the first argument is the base.

    log10

    Calculate the base-10 logarithm of a number.

    log2

    Calculate the base-2 logarithm of a number.

    multiply

    Multiply two values.

    nextafter

    Returns the next floating point value after x in the direction of y.

    pi

    Return the value of pi, about 3.14159.

    pow

    Raise a number to a power.

    power

    Raise a number to a power.

    radians

    Convert an angle from degrees to radians.

    random

    Return a random number between 0 and 1. Each row gets a new value.

    round

    Round decimal values to a consistent number of places for prices, measurements or reports.

    round_even

    Round a number, resolving exact halfway cases toward the nearest even digit.

    roundbankers

    Round a number using the half-to-even rule for exact halfway cases.

    sign

    Return -1 for a negative number, 0 for zero and 1 for a positive number.

    signbit

    Returns whether the signbit is set or not.

    sin

    Computes the sin of x.

    sqrt

    Calculate the square root of a number.

    subtract

    Subtract one value from another.

    tan

    Computes the tan of x.

    trunc

    Remove the fractional part of a number, rounding toward zero. An optional second argument keeps that many decimal places.

    xor

    Bitwise XOR.

    Dates & times

    age

    Calculate the time between two timestamps as an interval, subtracting the second from the first.

    century

    Get the century from a date or timestamp, such as 20 for 1992.

    date_add

    Move a date or timestamp forward or backward by an interval.

    date_diff

    Count the unit boundaries crossed between two dates or timestamps. For example, January 31 to February 1 crosses one month boundary.

    date_part

    Get one part of a date, time or interval, such as the year. Pass a list of part names to get a struct of parts.

    date_sub

    Count the complete units between two dates or timestamps. For example, January 31 to February 1 is zero complete months. Also available as datesub.

    date_trunc

    Round a date or timestamp down to the start of a unit such as month or year. Also available as datetrunc.

    datepart

    Get one part of a date, time or interval, such as the year. Pass a list of part names to get a struct of parts.

    day

    Get the day of the month, from 1 to 31, from a date or timestamp.

    dayname

    Get the English name of the weekday.

    dayofmonth

    Get the day of the month, from 1 to 31, from a date or timestamp.

    dayofweek

    Get the day of the week as a number, where Sunday is 0 and Saturday is 6.

    dayofyear

    Get the day of the year, from 1 to 366, from a date or timestamp.

    days_in_month

    Find the number of days in the month containing a date.

    decade

    Extract the decade component from a date or timestamp.

    epoch

    Get the seconds since 1970-01-01 for a date or timestamp, or the total seconds in an interval.

    epoch_ms

    Get the milliseconds since 1970-01-01 for a date or timestamp. Given a whole number of milliseconds, it returns the matching timestamp instead.

    epoch_ns

    Extract the epoch component in nanoseconds from a temporal type.

    epoch_us

    Extract the epoch component in microseconds from a temporal type.

    era

    Extract the era component from a date or timestamp.

    extract

    Extract a date or time component such as the year.

    generate_series

    Create a list of values from start to stop, including stop. Start defaults to 0 and step defaults to 1.

    get_current_timestamp

    Return the current date and time, with time zone.

    hour

    Get the hour, from 0 to 23, from a date or timestamp.

    isodow

    Get the ISO day of the week as a number, where Monday is 1 and Sunday is 7.

    isoyear

    Get the ISO week-numbering year, which can differ from the calendar year around New Year.

    julian

    Extract the Julian Day number from a date or timestamp.

    last_day

    Return the last day of the month containing a date.

    make_date

    Build a date from a year, month and day. It also accepts a struct with those fields or a day count since 1970-01-01.

    make_time

    Build a time from an hour, minute and seconds.

    make_timestamp

    Build a timestamp from a year, month, day, hour, minute and seconds.

    make_timestamp_ms

    Build a timestamp from the number of milliseconds since 1970-01-01.

    make_timestamp_ns

    The timestamp for the given nanoseconds since epoch.

    microsecond

    Extract the microsecond component from a date or timestamp.

    millennium

    Extract the millennium component from a date or timestamp.

    millisecond

    Extract the millisecond component from a date or timestamp.

    minute

    Get the minute, from 0 to 59, from a date or timestamp.

    month

    Get the month number, from 1 to 12, from a date or timestamp.

    monthname

    Get the English name of the month.

    now

    Return the current date and time, with time zone.

    quarter

    Get the quarter, from 1 to 4, from a date or timestamp.

    range

    Create a list of values from start up to stop, excluding stop. Start defaults to 0 and step defaults to 1.

    second

    Get the second, from 0 to 59, from a date or timestamp.

    strftime

    Format a date or timestamp as text using a format string such as %d/%m/%Y.

    strptime

    Parse text into a timestamp using a format string such as %d/%m/%Y. Text that doesn't match raises an error; use try_strptime to get a missing value instead.

    time_bucket

    Round a date or timestamp down to the start of a fixed-width bucket, such as every 2 days. Buckets count from 2000-01-03, or from 2000-01-01 for month and year buckets, unless you pass your own origin.

    timezone

    With one argument, get the time zone offset of a timestamp in seconds. With two arguments, convert a time or timestamp to a time zone given as a name, or as an interval for a time with time zone.

    timezone_hour

    Extract the timezone_hour component from a date or timestamp.

    timezone_minute

    Extract the timezone_minute component from a date or timestamp.

    to_centuries

    Construct a century interval.

    to_days

    Turn a number into an interval of that many days.

    to_decades

    Construct a decade interval.

    to_hours

    Construct an hour interval.

    to_microseconds

    Construct a microsecond interval.

    to_millennia

    Construct a millennium interval.

    to_milliseconds

    Construct a millisecond interval.

    to_minutes

    Construct a minute interval.

    to_months

    Turn a number into an interval of that many months.

    to_quarters

    Construct a quarter interval.

    to_seconds

    Construct a second interval.

    to_timestamp

    Convert seconds since 1970-01-01 to a timestamp with time zone.

    to_weeks

    Turn a number into an interval of that many weeks, shown in days.

    to_years

    Turn a number into an interval of that many years.

    transaction_timestamp

    Return the current date and time, with time zone.

    try_strptime

    Parse text into a timestamp using a format string such as %d/%m/%Y. Text that doesn't match returns a missing value instead of an error.

    week

    Get the ISO week number from a date or timestamp.

    weekday

    Get the day of the week as a number, where Sunday is 0 and Saturday is 6.

    weekofyear

    Get the ISO week number from a date or timestamp.

    year

    Get the year from a date or timestamp.

    yearweek

    Combine the ISO year and week number into one number, such as 199207 for week 7 of 1992.

    Conditions & missing values

    Patterns

    Across rows

    Lists & arrays

    array_cosine_distance

    Computes the cosine distance between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

    array_cosine_similarity

    Computes the cosine similarity between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

    array_cross_product

    Computes the cross product of two arrays of size 3. The array elements can not be NULL.

    array_distance

    Computes the distance between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

    array_extract

    Get one item from a list or one character from text by position, where 1 is the first. For a struct, choose a field by name, or by position in an unnamed struct.

    array_inner_product

    Computes the inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

    array_negative_inner_product

    Computes the negative inner product between two arrays of the same size. The array elements can not be NULL. The arrays can have any size as long as the size is the same for both arguments.

    array_pop_back

    Remove the last value from a list.

    array_pop_front

    Remove the first value from a list.

    array_push_front

    Add a value at the beginning of a list.

    array_slice

    Extract a section of a list or text by start and end positions, where 1 is the first position. Negative positions count from the end.

    array_to_string

    Join the values in a list into one string using a separator.

    array_to_string_comma_default

    Join the values in a list into comma-separated text.

    array_value

    Creates an ARRAY containing the argument values.

    flatten

    Merge a list of lists into one list, removing one level of nesting.

    list_aggregate

    Run a named aggregate function, such as min or sum, over the items in a list.

    list_any_value

    Take the first non-missing value from a list.

    list_append

    Add a value to the end of a list. Also available as array_append and array_push_back.

    list_approx_count_distinct

    Calculate an approximate count of distinct values from the values in a list.

    list_avg

    Calculate the arithmetic mean from the values in a list.

    list_bit_and

    Calculate the bitwise AND from the values in a list.

    list_bit_or

    Calculate the bitwise OR from the values in a list.

    list_bit_xor

    Calculate the bitwise XOR from the values in a list.

    list_bool_and

    Calculate whether all non-missing values are true from the values in a list.

    list_bool_or

    Calculate whether any non-missing value is true from the values in a list.

    list_concat

    Join lists into one list. Missing inputs are skipped, while the || operator returns a missing value if either input is missing.

    list_contains

    Check whether a list contains a value.

    list_cosine_distance

    Computes the cosine distance between two same-sized lists.

    list_cosine_similarity

    Computes the cosine similarity between two same-sized lists.

    list_count

    Calculate the number of non-missing values from the values in a list.

    list_distance

    Calculates the Euclidean distance between two points with coordinates given in two inputs lists of equal length.

    list_distinct

    Remove duplicates and missing values from a list. The original order isn't kept.

    list_entropy

    Calculate the information entropy from the values in a list.

    list_extract

    Get the list item at a position, where 1 is the first item. A negative position counts from the end.

    list_filter

    Keep the list items for which a lambda expression returns true. The lambda result must be convertible to BOOLEAN, and the output has the same type as the input list.

    list_first

    Calculate the first value from the values in a list.

    list_grade_up

    Sort a list and return the original positions of its items instead of their values. It accepts the same options as list_sort.

    list_has_all

    Check whether a list contains every item of a second list. Missing values are ignored.

    list_has_any

    Check whether two lists share at least one item. Missing values are ignored.

    list_histogram

    Calculate a frequency map from the values in a list.

    list_inner_product

    Computes the inner product between two same-sized lists.

    list_intersect

    Return the distinct items that appear in both lists.

    list_kurtosis

    Calculate the sample excess kurtosis from the values in a list.

    list_kurtosis_pop

    Calculate the population excess kurtosis from the values in a list.

    list_last

    Calculate the last value from the values in a list.

    list_mad

    Calculate the median absolute deviation from the values in a list.

    list_max

    Calculate the largest value from the values in a list.

    list_median

    Calculate the median from the values in a list.

    list_min

    Calculate the smallest value from the values in a list.

    list_mode

    Calculate the most frequent value from the values in a list.

    list_negative_inner_product

    Computes the negative inner product between two same-sized lists.

    list_position

    Find the position of a value in a list, where 1 is the first item. Returns a missing value if it isn't found.

    list_prepend

    Add a value to the beginning of a list. Also available as array_prepend.

    list_product

    Calculate the product from the values in a list.

    list_reduce

    Reduces all elements of the input list into a single scalar value by executing the lambda function on a running result and the next list element. The lambda function has an optional initial_value argument.

    list_resize

    Resize a list to the given size. New items take the given value, or a missing value if none is given.

    list_reverse

    Reverse the order of values in a list. Also available as array_reverse.

    list_reverse_sort

    Sort the items in a list in reverse order. An optional argument sets where missing values go (NULLS FIRST or NULLS LAST).

    list_select

    Build a list from the items at the given positions, where 1 is the first item.

    list_sem

    Calculate the standard error of the mean from the values in a list.

    list_skewness

    Calculate the sample skewness from the values in a list.

    list_slice

    Extract a section of a list or text by start and end positions, where 1 is the first position. Negative positions count from the end.

    list_sort

    Sort the items in a list. Optional arguments set the order (ASC or DESC) and where missing values go (NULLS FIRST or NULLS LAST).

    list_stddev_pop

    Calculate the population standard deviation from the values in a list.

    list_stddev_samp

    Calculate the sample standard deviation from the values in a list.

    list_string_agg

    Calculate comma-separated text from the values in a list.

    list_sum

    Add up the values in a list.

    list_transform

    Apply a lambda expression to each list item and return the results as a list. The item type of the output comes from the lambda result.

    list_unique

    Count the distinct items in a list, ignoring missing values.

    list_value

    Create a list from its arguments.

    list_var_pop

    Calculate the population variance from the values in a list.

    list_var_samp

    Calculate the sample variance from the values in a list.

    list_where

    Keep the list items whose matching position in a second list of true and false values is true.

    list_zip

    Pair up items by position from two or more lists, returning a list of structs. Shorter lists are padded with missing values unless you set truncate, which stops at the shortest list.

    Maps

    Structs

    Bits

    Binary data

    Enums

    Unions

    Conversion & identifiers

    DuckDB documentation