---
title: "Strata function reference | Oplema"
description: "Look up Strata functions for text, numbers, dates, lists and missing values, with signatures and verified examples for Formula and Filter."
url: "https://oplema.com/docs/strata/functions"
updated: "2026-11-04"
---

# Functions

Clean text, calculate values and write conditions. Use these expressions in [Formula](https://oplema.com/docs/strata/prep/formula) to create columns or in [Filter](https://oplema.com/docs/strata/prep/filter) to choose rows.

Find a function

[

Text 69

Clean, combine and compare text.

](https://oplema.com/docs/strata/functions#text)[

Numbers 56

Round values and perform calculations.

](https://oplema.com/docs/strata/functions#numbers)[

Dates & times 70

Parse dates, extract parts and calculate intervals.

](https://oplema.com/docs/strata/functions#dates-&-times)[

Conditions & missing values 8

Choose values and handle missing data.

](https://oplema.com/docs/strata/functions#conditions-&-missing-values)[

Patterns 10

Match and replace text using patterns.

](https://oplema.com/docs/strata/functions#patterns)[

Across rows 12

Rank rows and compare ordered values.

](https://oplema.com/docs/strata/functions#across-rows)[

Lists & arrays 74

Work with multiple values in one cell.

](https://oplema.com/docs/strata/functions#lists-&-arrays)[

Maps 13

Look up and combine key-value pairs.

](https://oplema.com/docs/strata/functions#maps)[

Structs 10

Build and read records within a cell.

](https://oplema.com/docs/strata/functions#structs)[

Bits 4

Inspect and change individual bits.

](https://oplema.com/docs/strata/functions#bits)[

Binary data 3

Convert between bytes and text.

](https://oplema.com/docs/strata/functions#binary-data)[

Enums 5

Inspect values from a fixed set of choices.

](https://oplema.com/docs/strata/functions#enums)[

Unions 3

Work with values that can have different types.

](https://oplema.com/docs/strata/functions#unions)[

Conversion & identifiers 10

Convert types and generate identifiers.

](https://oplema.com/docs/strata/functions#conversion-&-identifiers)

## All functions

347 functions

### Text

[ascii

Return the Unicode number of the first character in text.

](https://oplema.com/docs/strata/functions/ascii)[bin

Converts the string to binary representation.

](https://oplema.com/docs/strata/functions/bin)[bit\_length

Number of bits in a string.

](https://oplema.com/docs/strata/functions/bit-length)[chr

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

](https://oplema.com/docs/strata/functions/chr)[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.

](https://oplema.com/docs/strata/functions/concat)[concat\_ws

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

](https://oplema.com/docs/strata/functions/concat-ws)[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.

](https://oplema.com/docs/strata/functions/contains)[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.

](https://oplema.com/docs/strata/functions/damerau-levenshtein)[format

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

](https://oplema.com/docs/strata/functions/format)[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.

](https://oplema.com/docs/strata/functions/format-bytes)[from\_base64

Decode base64 text into binary data.

](https://oplema.com/docs/strata/functions/from-base64)[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.

](https://oplema.com/docs/strata/functions/hamming)[hash

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

](https://oplema.com/docs/strata/functions/hash)[hex

Convert a value to hexadecimal text.

](https://oplema.com/docs/strata/functions/hex)[ilike\_escape

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

](https://oplema.com/docs/strata/functions/ilike-escape)[instr

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

](https://oplema.com/docs/strata/functions/instr)[jaccard

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

](https://oplema.com/docs/strata/functions/jaccard)[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.

](https://oplema.com/docs/strata/functions/jaro-similarity)[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.

](https://oplema.com/docs/strata/functions/jaro-winkler-similarity)[left

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

](https://oplema.com/docs/strata/functions/left)[left\_grapheme

Extracts the left-most count grapheme clusters.

](https://oplema.com/docs/strata/functions/left-grapheme)[length

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

](https://oplema.com/docs/strata/functions/length)[length\_grapheme

Number of grapheme clusters in string.

](https://oplema.com/docs/strata/functions/length-grapheme)[levenshtein

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

](https://oplema.com/docs/strata/functions/levenshtein)[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 \_.

](https://oplema.com/docs/strata/functions/like-escape)[lower

Convert text to lowercase.

](https://oplema.com/docs/strata/functions/lower)[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.

](https://oplema.com/docs/strata/functions/lpad)[ltrim

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

](https://oplema.com/docs/strata/functions/ltrim)[md5

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

](https://oplema.com/docs/strata/functions/md5)[md5\_number

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

](https://oplema.com/docs/strata/functions/md5-number)[md5\_number\_lower

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

](https://oplema.com/docs/strata/functions/md5-number-lower)[md5\_number\_upper

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

](https://oplema.com/docs/strata/functions/md5-number-upper)[nfc\_normalize

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

](https://oplema.com/docs/strata/functions/nfc-normalize)[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 \_.

](https://oplema.com/docs/strata/functions/not-ilike-escape)[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 \_.

](https://oplema.com/docs/strata/functions/not-like-escape)[parse\_dirname

Return the top-level directory name from a path. The optional separator can be system, both\_slash (default), forward\_slash or backslash.

](https://oplema.com/docs/strata/functions/parse-dirname)[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.

](https://oplema.com/docs/strata/functions/parse-dirpath)[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.

](https://oplema.com/docs/strata/functions/parse-filename)[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.

](https://oplema.com/docs/strata/functions/parse-path)[position

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

](https://oplema.com/docs/strata/functions/position)[prefix

Check whether text begins with a given string.

](https://oplema.com/docs/strata/functions/prefix)[printf

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

](https://oplema.com/docs/strata/functions/printf)[repeat

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

](https://oplema.com/docs/strata/functions/repeat)[replace

Replace every occurrence of one piece of text with another.

](https://oplema.com/docs/strata/functions/replace)[reverse

Reverse the characters in text.

](https://oplema.com/docs/strata/functions/reverse)[right

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

](https://oplema.com/docs/strata/functions/right)[right\_grapheme

Extracts the right-most count grapheme clusters.

](https://oplema.com/docs/strata/functions/right-grapheme)[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.

](https://oplema.com/docs/strata/functions/rpad)[rtrim

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

](https://oplema.com/docs/strata/functions/rtrim)[sha1

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

](https://oplema.com/docs/strata/functions/sha1)[sha256

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

](https://oplema.com/docs/strata/functions/sha256)[split\_part

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

](https://oplema.com/docs/strata/functions/split-part)[starts\_with

Check whether text begins with a given string.

](https://oplema.com/docs/strata/functions/starts-with)[string\_split

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

](https://oplema.com/docs/strata/functions/string-split)[strip\_accents

Remove accents from text, so ü becomes u.

](https://oplema.com/docs/strata/functions/strip-accents)[strlen

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

](https://oplema.com/docs/strata/functions/strlen)[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.

](https://oplema.com/docs/strata/functions/substring)[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.

](https://oplema.com/docs/strata/functions/substring-grapheme)[suffix

Check whether text ends with a given string.

](https://oplema.com/docs/strata/functions/suffix)[to\_base

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

](https://oplema.com/docs/strata/functions/to-base)[to\_base64

Encode binary data as base64 text.

](https://oplema.com/docs/strata/functions/to-base64)[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.

](https://oplema.com/docs/strata/functions/translate)[trim

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

](https://oplema.com/docs/strata/functions/trim)[unbin

Converts a value from binary representation to a blob.

](https://oplema.com/docs/strata/functions/unbin)[unhex

Converts a value from hexadecimal representation to a blob.

](https://oplema.com/docs/strata/functions/unhex)[unicode

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

](https://oplema.com/docs/strata/functions/unicode)[upper

Standardize text in uppercase before comparing labels or displaying codes.

](https://oplema.com/docs/strata/functions/upper)[url\_decode

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

](https://oplema.com/docs/strata/functions/url-decode)[url\_encode

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

](https://oplema.com/docs/strata/functions/url-encode)

### Numbers

[abs

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

](https://oplema.com/docs/strata/functions/abs)[acos

Computes the arccosine of x.

](https://oplema.com/docs/strata/functions/acos)[acosh

Computes the inverse hyperbolic cos of x.

](https://oplema.com/docs/strata/functions/acosh)[add

Add two values.

](https://oplema.com/docs/strata/functions/add)[asin

Computes the arcsine of x.

](https://oplema.com/docs/strata/functions/asin)[asinh

Computes the inverse hyperbolic sin of x.

](https://oplema.com/docs/strata/functions/asinh)[atan

Computes the arctangent of x.

](https://oplema.com/docs/strata/functions/atan)[atan2

Computes the arctangent (y, x).

](https://oplema.com/docs/strata/functions/atan2)[atanh

Computes the inverse hyperbolic tan of x.

](https://oplema.com/docs/strata/functions/atanh)[bit\_count

Returns the number of bits that are set.

](https://oplema.com/docs/strata/functions/bit-count)[cbrt

Calculate the cube root of a number.

](https://oplema.com/docs/strata/functions/cbrt)[ceil

Round a number up to the nearest whole number.

](https://oplema.com/docs/strata/functions/ceil)[ceiling

Round a number up to the nearest whole number.

](https://oplema.com/docs/strata/functions/ceiling)[cos

Computes the cos of x.

](https://oplema.com/docs/strata/functions/cos)[cot

Computes the cotangent of x.

](https://oplema.com/docs/strata/functions/cot)[degrees

Convert an angle from radians to degrees.

](https://oplema.com/docs/strata/functions/degrees)[divide

Divide whole numbers and discard the fractional part.

](https://oplema.com/docs/strata/functions/divide)[even

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

](https://oplema.com/docs/strata/functions/even)[exp

Raise e, about 2.718, to a given power.

](https://oplema.com/docs/strata/functions/exp)[factorial

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

](https://oplema.com/docs/strata/functions/factorial)[fdiv

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

](https://oplema.com/docs/strata/functions/fdiv)[floor

Round a number down to the nearest whole number.

](https://oplema.com/docs/strata/functions/floor)[fmod

Find the remainder using division rounded down toward negative infinity.

](https://oplema.com/docs/strata/functions/fmod)[gamma

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

](https://oplema.com/docs/strata/functions/gamma)[gcd

Find the largest whole number that divides two numbers exactly.

](https://oplema.com/docs/strata/functions/gcd)[greatest

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

](https://oplema.com/docs/strata/functions/greatest)[greatest\_common\_divisor

Find the largest whole number that divides two numbers exactly.

](https://oplema.com/docs/strata/functions/greatest-common-divisor)[isfinite

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

](https://oplema.com/docs/strata/functions/isfinite)[isinf

Check whether a value is infinite.

](https://oplema.com/docs/strata/functions/isinf)[isnan

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

](https://oplema.com/docs/strata/functions/isnan)[lcm

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

](https://oplema.com/docs/strata/functions/lcm)[least

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

](https://oplema.com/docs/strata/functions/least)[least\_common\_multiple

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

](https://oplema.com/docs/strata/functions/least-common-multiple)[lgamma

Computes the log of the gamma function.

](https://oplema.com/docs/strata/functions/lgamma)[ln

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

](https://oplema.com/docs/strata/functions/ln)[log

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

](https://oplema.com/docs/strata/functions/log)[log10

Calculate the base-10 logarithm of a number.

](https://oplema.com/docs/strata/functions/log10)[log2

Calculate the base-2 logarithm of a number.

](https://oplema.com/docs/strata/functions/log2)[multiply

Multiply two values.

](https://oplema.com/docs/strata/functions/multiply)[nextafter

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

](https://oplema.com/docs/strata/functions/nextafter)[pi

Return the value of pi, about 3.14159.

](https://oplema.com/docs/strata/functions/pi)[pow

Raise a number to a power.

](https://oplema.com/docs/strata/functions/pow)[power

Raise a number to a power.

](https://oplema.com/docs/strata/functions/power)[radians

Convert an angle from degrees to radians.

](https://oplema.com/docs/strata/functions/radians)[random

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

](https://oplema.com/docs/strata/functions/random)[round

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

](https://oplema.com/docs/strata/functions/round)[round\_even

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

](https://oplema.com/docs/strata/functions/round-even)[roundbankers

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

](https://oplema.com/docs/strata/functions/roundbankers)[sign

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

](https://oplema.com/docs/strata/functions/sign)[signbit

Returns whether the signbit is set or not.

](https://oplema.com/docs/strata/functions/signbit)[sin

Computes the sin of x.

](https://oplema.com/docs/strata/functions/sin)[sqrt

Calculate the square root of a number.

](https://oplema.com/docs/strata/functions/sqrt)[subtract

Subtract one value from another.

](https://oplema.com/docs/strata/functions/subtract)[tan

Computes the tan of x.

](https://oplema.com/docs/strata/functions/tan)[trunc

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

](https://oplema.com/docs/strata/functions/trunc)[xor

Bitwise XOR.

](https://oplema.com/docs/strata/functions/xor)

### Dates & times

[age

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

](https://oplema.com/docs/strata/functions/age)[century

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

](https://oplema.com/docs/strata/functions/century)[date\_add

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

](https://oplema.com/docs/strata/functions/date-add)[date\_diff

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

](https://oplema.com/docs/strata/functions/date-diff)[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.

](https://oplema.com/docs/strata/functions/date-part)[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.

](https://oplema.com/docs/strata/functions/date-sub)[date\_trunc

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

](https://oplema.com/docs/strata/functions/date-trunc)[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.

](https://oplema.com/docs/strata/functions/datepart)[day

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

](https://oplema.com/docs/strata/functions/day)[dayname

Get the English name of the weekday.

](https://oplema.com/docs/strata/functions/dayname)[dayofmonth

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

](https://oplema.com/docs/strata/functions/dayofmonth)[dayofweek

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

](https://oplema.com/docs/strata/functions/dayofweek)[dayofyear

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

](https://oplema.com/docs/strata/functions/dayofyear)[days\_in\_month

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

](https://oplema.com/docs/strata/functions/days-in-month)[decade

Extract the decade component from a date or timestamp.

](https://oplema.com/docs/strata/functions/decade)[epoch

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

](https://oplema.com/docs/strata/functions/epoch)[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.

](https://oplema.com/docs/strata/functions/epoch-ms)[epoch\_ns

Extract the epoch component in nanoseconds from a temporal type.

](https://oplema.com/docs/strata/functions/epoch-ns)[epoch\_us

Extract the epoch component in microseconds from a temporal type.

](https://oplema.com/docs/strata/functions/epoch-us)[era

Extract the era component from a date or timestamp.

](https://oplema.com/docs/strata/functions/era)[extract

Extract a date or time component such as the year.

](https://oplema.com/docs/strata/functions/extract)[generate\_series

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

](https://oplema.com/docs/strata/functions/generate-series)[get\_current\_timestamp

Return the current date and time, with time zone.

](https://oplema.com/docs/strata/functions/get-current-timestamp)[hour

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

](https://oplema.com/docs/strata/functions/hour)[isodow

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

](https://oplema.com/docs/strata/functions/isodow)[isoyear

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

](https://oplema.com/docs/strata/functions/isoyear)[julian

Extract the Julian Day number from a date or timestamp.

](https://oplema.com/docs/strata/functions/julian)[last\_day

Return the last day of the month containing a date.

](https://oplema.com/docs/strata/functions/last-day)[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.

](https://oplema.com/docs/strata/functions/make-date)[make\_time

Build a time from an hour, minute and seconds.

](https://oplema.com/docs/strata/functions/make-time)[make\_timestamp

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

](https://oplema.com/docs/strata/functions/make-timestamp)[make\_timestamp\_ms

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

](https://oplema.com/docs/strata/functions/make-timestamp-ms)[make\_timestamp\_ns

The timestamp for the given nanoseconds since epoch.

](https://oplema.com/docs/strata/functions/make-timestamp-ns)[microsecond

Extract the microsecond component from a date or timestamp.

](https://oplema.com/docs/strata/functions/microsecond)[millennium

Extract the millennium component from a date or timestamp.

](https://oplema.com/docs/strata/functions/millennium)[millisecond

Extract the millisecond component from a date or timestamp.

](https://oplema.com/docs/strata/functions/millisecond)[minute

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

](https://oplema.com/docs/strata/functions/minute)[month

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

](https://oplema.com/docs/strata/functions/month)[monthname

Get the English name of the month.

](https://oplema.com/docs/strata/functions/monthname)[now

Return the current date and time, with time zone.

](https://oplema.com/docs/strata/functions/now)[quarter

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

](https://oplema.com/docs/strata/functions/quarter)[range

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

](https://oplema.com/docs/strata/functions/range)[second

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

](https://oplema.com/docs/strata/functions/second)[strftime

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

](https://oplema.com/docs/strata/functions/strftime)[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.

](https://oplema.com/docs/strata/functions/strptime)[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.

](https://oplema.com/docs/strata/functions/time-bucket)[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.

](https://oplema.com/docs/strata/functions/timezone)[timezone\_hour

Extract the timezone\_hour component from a date or timestamp.

](https://oplema.com/docs/strata/functions/timezone-hour)[timezone\_minute

Extract the timezone\_minute component from a date or timestamp.

](https://oplema.com/docs/strata/functions/timezone-minute)[to\_centuries

Construct a century interval.

](https://oplema.com/docs/strata/functions/to-centuries)[to\_days

Turn a number into an interval of that many days.

](https://oplema.com/docs/strata/functions/to-days)[to\_decades

Construct a decade interval.

](https://oplema.com/docs/strata/functions/to-decades)[to\_hours

Construct an hour interval.

](https://oplema.com/docs/strata/functions/to-hours)[to\_microseconds

Construct a microsecond interval.

](https://oplema.com/docs/strata/functions/to-microseconds)[to\_millennia

Construct a millennium interval.

](https://oplema.com/docs/strata/functions/to-millennia)[to\_milliseconds

Construct a millisecond interval.

](https://oplema.com/docs/strata/functions/to-milliseconds)[to\_minutes

Construct a minute interval.

](https://oplema.com/docs/strata/functions/to-minutes)[to\_months

Turn a number into an interval of that many months.

](https://oplema.com/docs/strata/functions/to-months)[to\_quarters

Construct a quarter interval.

](https://oplema.com/docs/strata/functions/to-quarters)[to\_seconds

Construct a second interval.

](https://oplema.com/docs/strata/functions/to-seconds)[to\_timestamp

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

](https://oplema.com/docs/strata/functions/to-timestamp)[to\_weeks

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

](https://oplema.com/docs/strata/functions/to-weeks)[to\_years

Turn a number into an interval of that many years.

](https://oplema.com/docs/strata/functions/to-years)[transaction\_timestamp

Return the current date and time, with time zone.

](https://oplema.com/docs/strata/functions/transaction-timestamp)[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.

](https://oplema.com/docs/strata/functions/try-strptime)[week

Get the ISO week number from a date or timestamp.

](https://oplema.com/docs/strata/functions/week)[weekday

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

](https://oplema.com/docs/strata/functions/weekday)[weekofyear

Get the ISO week number from a date or timestamp.

](https://oplema.com/docs/strata/functions/weekofyear)[year

Get the year from a date or timestamp.

](https://oplema.com/docs/strata/functions/year)[yearweek

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

](https://oplema.com/docs/strata/functions/yearweek)

### Conditions & missing values

[BETWEEN

Check whether a value falls within an inclusive range.

](https://oplema.com/docs/strata/functions/between)[CASE

Choose a result using one or more conditions.

](https://oplema.com/docs/strata/functions/case)[coalesce

Replace missing values with the first available alternative.

](https://oplema.com/docs/strata/functions/coalesce)[if

Choose between two values using a condition.

](https://oplema.com/docs/strata/functions/if)[ifnull

Use a fallback when a value is missing.

](https://oplema.com/docs/strata/functions/ifnull)[IS NULL

Find missing values without confusing them with empty text.

](https://oplema.com/docs/strata/functions/is-null)[nullif

Turn a sentinel value, such as zero or an empty string, into a missing value.

](https://oplema.com/docs/strata/functions/nullif)[Operators

Calculate values and combine comparisons in expressions.

](https://oplema.com/docs/strata/functions/operators)

### Patterns

[ILIKE

Match text against a pattern without distinguishing uppercase and lowercase.

](https://oplema.com/docs/strata/functions/ilike)[LIKE

Match text against a case-sensitive pattern.

](https://oplema.com/docs/strata/functions/like)[regexp\_escape

Escape special characters in text so that a regular expression matches it literally.

](https://oplema.com/docs/strata/functions/regexp-escape)[regexp\_extract

Return the part of text that matches a regular expression, or empty text if there is no match. An optional constant group number picks a capturing group, and regex options can follow.

](https://oplema.com/docs/strata/functions/regexp-extract)[regexp\_extract\_all

Find every non-overlapping match of a regular expression in text and return them as a list. An optional group number picks a capturing group, and regex options can follow.

](https://oplema.com/docs/strata/functions/regexp-extract-all)[regexp\_full\_match

Check whether all of the text matches a regular expression. An optional argument sets regex options.

](https://oplema.com/docs/strata/functions/regexp-full-match)[regexp\_matches

Check whether text contains a match for a regular expression. An optional argument sets regex options.

](https://oplema.com/docs/strata/functions/regexp-matches)[regexp\_replace

Replace the first match of a regular expression in text. Pass the g option to replace every match.

](https://oplema.com/docs/strata/functions/regexp-replace)[regexp\_split\_to\_array

Split text at each match of a regular expression and return the parts as a list. An optional argument sets regex options.

](https://oplema.com/docs/strata/functions/regexp-split-to-array)[string\_split\_regex

Split text at each match of a regular expression and return the parts as a list. An optional argument sets regex options.

](https://oplema.com/docs/strata/functions/string-split-regex)

### Across rows

[cume\_dist

Find the fraction of rows whose sort value is less than or equal to the current value.

](https://oplema.com/docs/strata/functions/cume-dist)[dense\_rank

Rank distinct values consecutively, giving ties the same rank.

](https://oplema.com/docs/strata/functions/dense-rank)[fill

Estimate missing values using linear interpolation along an ordered sequence.

](https://oplema.com/docs/strata/functions/fill)[first\_value

Repeat the first value in a window frame.

](https://oplema.com/docs/strata/functions/first-value)[lag

Compare each row with a value from an earlier row.

](https://oplema.com/docs/strata/functions/lag)[last\_value

Repeat the last value in a window frame.

](https://oplema.com/docs/strata/functions/last-value)[lead

Compare each row with a value from a later row.

](https://oplema.com/docs/strata/functions/lead)[nth\_value

Retrieve a value at a specified position within a window frame.

](https://oplema.com/docs/strata/functions/nth-value)[ntile

Divide ordered rows into a requested number of approximately equal groups.

](https://oplema.com/docs/strata/functions/ntile)[percent\_rank

Express a row rank on a scale from zero to one.

](https://oplema.com/docs/strata/functions/percent-rank)[rank

Rank values while leaving gaps after ties.

](https://oplema.com/docs/strata/functions/rank)[row\_number

Number rows in a specified order.

](https://oplema.com/docs/strata/functions/row-number)

### 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.

](https://oplema.com/docs/strata/functions/array-cosine-distance)[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.

](https://oplema.com/docs/strata/functions/array-cosine-similarity)[array\_cross\_product

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

](https://oplema.com/docs/strata/functions/array-cross-product)[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.

](https://oplema.com/docs/strata/functions/array-distance)[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.

](https://oplema.com/docs/strata/functions/array-extract)[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.

](https://oplema.com/docs/strata/functions/array-inner-product)[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.

](https://oplema.com/docs/strata/functions/array-negative-inner-product)[array\_pop\_back

Remove the last value from a list.

](https://oplema.com/docs/strata/functions/array-pop-back)[array\_pop\_front

Remove the first value from a list.

](https://oplema.com/docs/strata/functions/array-pop-front)[array\_push\_front

Add a value at the beginning of a list.

](https://oplema.com/docs/strata/functions/array-push-front)[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.

](https://oplema.com/docs/strata/functions/array-slice)[array\_to\_string

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

](https://oplema.com/docs/strata/functions/array-to-string)[array\_to\_string\_comma\_default

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

](https://oplema.com/docs/strata/functions/array-to-string-comma-default)[array\_value

Creates an ARRAY containing the argument values.

](https://oplema.com/docs/strata/functions/array-value)[flatten

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

](https://oplema.com/docs/strata/functions/flatten)[list\_aggregate

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

](https://oplema.com/docs/strata/functions/list-aggregate)[list\_any\_value

Take the first non-missing value from a list.

](https://oplema.com/docs/strata/functions/list-any-value)[list\_append

Add a value to the end of a list. Also available as array\_append and array\_push\_back.

](https://oplema.com/docs/strata/functions/list-append)[list\_approx\_count\_distinct

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

](https://oplema.com/docs/strata/functions/list-approx-count-distinct)[list\_avg

Calculate the arithmetic mean from the values in a list.

](https://oplema.com/docs/strata/functions/list-avg)[list\_bit\_and

Calculate the bitwise AND from the values in a list.

](https://oplema.com/docs/strata/functions/list-bit-and)[list\_bit\_or

Calculate the bitwise OR from the values in a list.

](https://oplema.com/docs/strata/functions/list-bit-or)[list\_bit\_xor

Calculate the bitwise XOR from the values in a list.

](https://oplema.com/docs/strata/functions/list-bit-xor)[list\_bool\_and

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

](https://oplema.com/docs/strata/functions/list-bool-and)[list\_bool\_or

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

](https://oplema.com/docs/strata/functions/list-bool-or)[list\_concat

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

](https://oplema.com/docs/strata/functions/list-concat)[list\_contains

Check whether a list contains a value.

](https://oplema.com/docs/strata/functions/list-contains)[list\_cosine\_distance

Computes the cosine distance between two same-sized lists.

](https://oplema.com/docs/strata/functions/list-cosine-distance)[list\_cosine\_similarity

Computes the cosine similarity between two same-sized lists.

](https://oplema.com/docs/strata/functions/list-cosine-similarity)[list\_count

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

](https://oplema.com/docs/strata/functions/list-count)[list\_distance

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

](https://oplema.com/docs/strata/functions/list-distance)[list\_distinct

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

](https://oplema.com/docs/strata/functions/list-distinct)[list\_entropy

Calculate the information entropy from the values in a list.

](https://oplema.com/docs/strata/functions/list-entropy)[list\_extract

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

](https://oplema.com/docs/strata/functions/list-extract)[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.

](https://oplema.com/docs/strata/functions/list-filter)[list\_first

Calculate the first value from the values in a list.

](https://oplema.com/docs/strata/functions/list-first)[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.

](https://oplema.com/docs/strata/functions/list-grade-up)[list\_has\_all

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

](https://oplema.com/docs/strata/functions/list-has-all)[list\_has\_any

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

](https://oplema.com/docs/strata/functions/list-has-any)[list\_histogram

Calculate a frequency map from the values in a list.

](https://oplema.com/docs/strata/functions/list-histogram)[list\_inner\_product

Computes the inner product between two same-sized lists.

](https://oplema.com/docs/strata/functions/list-inner-product)[list\_intersect

Return the distinct items that appear in both lists.

](https://oplema.com/docs/strata/functions/list-intersect)[list\_kurtosis

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

](https://oplema.com/docs/strata/functions/list-kurtosis)[list\_kurtosis\_pop

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

](https://oplema.com/docs/strata/functions/list-kurtosis-pop)[list\_last

Calculate the last value from the values in a list.

](https://oplema.com/docs/strata/functions/list-last)[list\_mad

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

](https://oplema.com/docs/strata/functions/list-mad)[list\_max

Calculate the largest value from the values in a list.

](https://oplema.com/docs/strata/functions/list-max)[list\_median

Calculate the median from the values in a list.

](https://oplema.com/docs/strata/functions/list-median)[list\_min

Calculate the smallest value from the values in a list.

](https://oplema.com/docs/strata/functions/list-min)[list\_mode

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

](https://oplema.com/docs/strata/functions/list-mode)[list\_negative\_inner\_product

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

](https://oplema.com/docs/strata/functions/list-negative-inner-product)[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.

](https://oplema.com/docs/strata/functions/list-position)[list\_prepend

Add a value to the beginning of a list. Also available as array\_prepend.

](https://oplema.com/docs/strata/functions/list-prepend)[list\_product

Calculate the product from the values in a list.

](https://oplema.com/docs/strata/functions/list-product)[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.

](https://oplema.com/docs/strata/functions/list-reduce)[list\_resize

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

](https://oplema.com/docs/strata/functions/list-resize)[list\_reverse

Reverse the order of values in a list. Also available as array\_reverse.

](https://oplema.com/docs/strata/functions/list-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).

](https://oplema.com/docs/strata/functions/list-reverse-sort)[list\_select

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

](https://oplema.com/docs/strata/functions/list-select)[list\_sem

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

](https://oplema.com/docs/strata/functions/list-sem)[list\_skewness

Calculate the sample skewness from the values in a list.

](https://oplema.com/docs/strata/functions/list-skewness)[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.

](https://oplema.com/docs/strata/functions/list-slice)[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).

](https://oplema.com/docs/strata/functions/list-sort)[list\_stddev\_pop

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

](https://oplema.com/docs/strata/functions/list-stddev-pop)[list\_stddev\_samp

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

](https://oplema.com/docs/strata/functions/list-stddev-samp)[list\_string\_agg

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

](https://oplema.com/docs/strata/functions/list-string-agg)[list\_sum

Add up the values in a list.

](https://oplema.com/docs/strata/functions/list-sum)[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.

](https://oplema.com/docs/strata/functions/list-transform)[list\_unique

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

](https://oplema.com/docs/strata/functions/list-unique)[list\_value

Create a list from its arguments.

](https://oplema.com/docs/strata/functions/list-value)[list\_var\_pop

Calculate the population variance from the values in a list.

](https://oplema.com/docs/strata/functions/list-var-pop)[list\_var\_samp

Calculate the sample variance from the values in a list.

](https://oplema.com/docs/strata/functions/list-var-samp)[list\_where

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

](https://oplema.com/docs/strata/functions/list-where)[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.

](https://oplema.com/docs/strata/functions/list-zip)

### Maps

[cardinality

Count the entries in a map.

](https://oplema.com/docs/strata/functions/cardinality)[element\_at

Look up a key in a map and return its value inside a list, or an empty list if the key is absent. The key must have the same type as the map's keys.

](https://oplema.com/docs/strata/functions/element-at)[map

Create a map from a list of keys and a list of values.

](https://oplema.com/docs/strata/functions/map)[map\_concat

Merge maps into one. If a key appears more than once, the value from the last map with that key wins.

](https://oplema.com/docs/strata/functions/map-concat)[map\_contains

Check whether a map has a given key.

](https://oplema.com/docs/strata/functions/map-contains)[map\_contains\_entry

Check whether a map includes a particular key and value together.

](https://oplema.com/docs/strata/functions/map-contains-entry)[map\_contains\_value

Check whether any entry in a map has the requested value.

](https://oplema.com/docs/strata/functions/map-contains-value)[map\_entries

Return a map's entries as a list of structs with key and value fields.

](https://oplema.com/docs/strata/functions/map-entries)[map\_extract

Look up a key in a map and return its value inside a list, or an empty list if the key is absent. The key must have the same type as the map's keys.

](https://oplema.com/docs/strata/functions/map-extract)[map\_extract\_value

Look up a key in a map and return its value, or a missing value if the key is absent. The key must have the same type as the map's keys.

](https://oplema.com/docs/strata/functions/map-extract-value)[map\_from\_entries

Create a map from a list of structs that each hold a key and a value.

](https://oplema.com/docs/strata/functions/map-from-entries)[map\_keys

List the keys of a map.

](https://oplema.com/docs/strata/functions/map-keys)[map\_values

List the values of a map.

](https://oplema.com/docs/strata/functions/map-values)

### Structs

[row

Create an unnamed struct from its arguments.

](https://oplema.com/docs/strata/functions/row)[struct\_concat

Merge several structs into one.

](https://oplema.com/docs/strata/functions/struct-concat)[struct\_contains

Check whether an unnamed struct contains a value.

](https://oplema.com/docs/strata/functions/struct-contains)[struct\_extract

Get one entry from a struct by name, or by position starting at 1 in an unnamed struct.

](https://oplema.com/docs/strata/functions/struct-extract)[struct\_extract\_at

Get one entry from a struct by position, starting at 1.

](https://oplema.com/docs/strata/functions/struct-extract-at)[struct\_insert

Add fields to an existing struct. Each new field takes its name from a named argument, as in struct\_insert(s, b := 2).

](https://oplema.com/docs/strata/functions/struct-insert)[struct\_pack

Create a struct from named arguments. Each field takes its name from its argument, as in struct\_pack(a := 1).

](https://oplema.com/docs/strata/functions/struct-pack)[struct\_position

Find the position of a value in an unnamed struct, starting at 1.

](https://oplema.com/docs/strata/functions/struct-position)[struct\_update

Change or add fields in an existing struct. Each field takes its name from a named argument, as in struct\_update(s, b := 2).

](https://oplema.com/docs/strata/functions/struct-update)[struct\_values

Return the field values of a struct as an unnamed struct.

](https://oplema.com/docs/strata/functions/struct-values)

### Bits

[bit\_position

Returns first starting index of the specified substring within bits, or zero if it isn't present. The first (leftmost) bit is indexed 1.

](https://oplema.com/docs/strata/functions/bit-position)[bitstring

Pads the bitstring until the specified length.

](https://oplema.com/docs/strata/functions/bitstring)[get\_bit

Extracts the nth bit from bitstring; the first (leftmost) bit is indexed 0.

](https://oplema.com/docs/strata/functions/get-bit)[set\_bit

Sets the nth bit in bitstring to newvalue; the first (leftmost) bit is indexed 0. Returns a new bitstring.

](https://oplema.com/docs/strata/functions/set-bit)

### Binary data

[decode

Converts blob to VARCHAR. Invalid UTF-8 is handled based on the error behavior argument. Can be 'strict' (default, fail), 'replace' to replace invalid characters with '?', or 'ignore' to skip invalid characters.

](https://oplema.com/docs/strata/functions/decode)[encode

Converts the string to BLOB. Converts UTF-8 characters into literal encoding.

](https://oplema.com/docs/strata/functions/encode)[octet\_length

Number of bytes in blob.

](https://oplema.com/docs/strata/functions/octet-length)

### Enums

[enum\_code

Returns the numeric value backing the given enum value.

](https://oplema.com/docs/strata/functions/enum-code)[enum\_first

Returns the first value of the input enum type.

](https://oplema.com/docs/strata/functions/enum-first)[enum\_last

Returns the last value of the input enum type.

](https://oplema.com/docs/strata/functions/enum-last)[enum\_range

Returns all values of the input enum type as an array.

](https://oplema.com/docs/strata/functions/enum-range)[enum\_range\_boundary

Returns the range between the two given enum values as an array. The values must be of the same enum type. When the first parameter is NULL, the result starts with the first value of the enum type. When the second parameter is NULL, the result ends with the last value of the enum type.

](https://oplema.com/docs/strata/functions/enum-range-boundary)

### Unions

[union\_extract

Extract the value with the named tags from the union. NULL if the tag isn't currently selected.

](https://oplema.com/docs/strata/functions/union-extract)[union\_tag

Retrieve the currently selected tag of the union as an ENUM.

](https://oplema.com/docs/strata/functions/union-tag)[union\_value

Create a union with one member. The tag comes from the name of the argument, as in union\_value(k := 1).

](https://oplema.com/docs/strata/functions/union-value)

### Conversion & identifiers

[CAST

Convert a value to another data type; invalid conversions raise an error.

](https://oplema.com/docs/strata/functions/cast)[constant\_or\_null

Return the first argument, or a missing value if the second argument is missing.

](https://oplema.com/docs/strata/functions/constant-or-null)[gen\_random\_uuid

Return a random UUID v4, such as 7e7ea35f-f813-46ce-ac7b-12658c021bcb.

](https://oplema.com/docs/strata/functions/gen-random-uuid)[TRY\_CAST

Convert a value to another data type, returning a missing value if conversion fails.

](https://oplema.com/docs/strata/functions/try-cast)[typeof

Return the name of an expression's data type.

](https://oplema.com/docs/strata/functions/typeof)[uuid

Return a random UUID v4, such as 7e7ea35f-f813-46ce-ac7b-12658c021bcb.

](https://oplema.com/docs/strata/functions/uuid)[uuid\_extract\_timestamp

Extract the timestamp for the given UUID v7.

](https://oplema.com/docs/strata/functions/uuid-extract-timestamp)[uuid\_extract\_version

Extract a version for the given UUID.

](https://oplema.com/docs/strata/functions/uuid-extract-version)[uuidv4

Return a random UUID v4, such as 7e7ea35f-f813-46ce-ac7b-12658c021bcb.

](https://oplema.com/docs/strata/functions/uuidv4)[uuidv7

Return a random, time-ordered UUID v7, such as 019482e4-1441-7aad-8127-eec99573b0a0.

](https://oplema.com/docs/strata/functions/uuidv7)

[DuckDB documentation](https://duckdb.org/docs/stable/sql/functions/overview)
