expressions-list.md

title: List of expressions
redirect_from:
  - /docs/latest/users-guide/expressions-list

List of expressions

For an introduction to expressions, check out Writing expressions in the notebook editor.

• Aggregations
  • Average
  • Count
  • CountIf
  • CumulativeCount
  • CumulativeSum
  • Distinct
  • Max
  • Median
  • Min
  • Percentile
  • Share
  • StandardDeviation
  • Sum
  • SumIf
  • Variance
• Functions
  • abs
  • between
  • case
  • ceil
  • coalesce
  • concat
  • contains
  • endswith
  • exp
  • floor
  • interval
  • isempty
  • isnull
  • lefttrim
  • length
  • log
  • lower
  • power
  • regexextract
  • replace
  • righttrim
  • round
  • sqrt
  • startswith
  • substring
  • trim
  • upper
• Database limitations

Aggregations

Aggregation expressions take into account all values in a field. They can only be used in the Summarize section of the notebook editor.

Average

Returns the average of the values in the column.

Syntax: Average(column)

Example: Average([Quantity]) would return the mean for the Quantity field.

Count

Returns the count of rows (also known as records) in the selected data.

Syntax: Count

Example: Count If a table or result returns 10 rows, Count will return 10.

CountIf

Only counts rows where the condition is true.

Syntax: CountIf(condition).

Example: CountIf([Subtotal] > 100) would return the number of rows where the subtotal were greater than 100.

CumulativeCount

The additive total of rows across a breakout.

Syntax: CumulativeCount.

Example: CumulativeCount.

CumulativeSum

The rolling sum of a column across a breakout.

Syntax: CumulativeSum(column).

Example: CumulativeSum([Subtotal]).

Related: Sum and SumIf.

Distinct

The number of distinct values in this column.

Syntax: Distinct(column).

Distinct([Last Name]). Returns the count of unique last names in the column. Duplicates (of the last name "Smith" for example) are not counted.

Max

Returns the largest value found in the column.

Syntax: Max(column).

Example: Max([Age]) would return the oldest age found across all values in the Age column.

Related: Min, Average, Median.

Median

Returns the median value of the specified column.

Syntax: Median(column).

Example: Median([Age]) would find the midpoint age where half of the ages are older, and half of the ages are younger.

Databases that don't support median: SQLite, Vertica, SQL server, MySQL. Presto only provides approximate results.

Related: Min, Max, Average.

Min

Returns the smallest value found in the column.

Syntax: Min(column).

Example: Min([Salary]) would find the lowest salary among all salaries in the Salary column.

Related: Max, Median, Average.

Percentile

Returns the value of the column at the percentile value.

Syntax: Percentile(column, percentile-value)

Example: Percentile([Score], 0.9) would return the value at the 90th percentile for all values in that column.

Databases that don't support percentile: H2, MySQL, SQL Server, SQLite, Vertica. Presto only provides approximate results.

Share

Returns the percent of rows in the data that match the condition, as a decimal.

Syntax: Share(condition)

Example: Share([Color] = "Blue") would return the number of rows with the Color field set to Blue, divided by the total number of rows.

StandardDeviation

Calculates the standard deviation of the column, which is a measure of the variation in a set of values. Low standard deviation indicates values cluster around the mean, whereas a high standard deviation means the values are spread out over a wide range.

Syntax: StandardDeviation(column)

Example: StandardDeviation([Population]) would return the SD for the values in the Population column.

Sum

Adds up all the values of the column.

Syntax: Sum(column)

Example: Sum([Subtotal]) would add up all the values in the Subtotal column.

SumIf

Sums up the specified column only for rows where the condition is true.

Syntax: SumIf(column, condition).

Example:SumIf([Subtotal], [Order Status] = "Valid") would add up all the subtotals for orders with a status of "Valid".

Variance

Returns the numeric variance for a given column.

Syntax: Variance(column)

Example: Variance([Temperature]) will return a measure of the dispersion from the mean temperature for all temps in that column.

Related: StandardDeviation, Average.

Functions

Function expressions apply to each individual value. They can be used to alter or filter values in a column, or create new, custom columns.

abs

Returns the absolute (positive) value of the specified column.

Syntax: abs(column)

Example: abs([Debt]). If Debt were -100, abs(-100) would return 100.

between

Checks a date or number column's values to see if they're within the specified range.

Syntax: between(column, start, end)

Example: between([Created At], "2019-01-01", "2020-12-31") would return rows where Created At date fell within the range of January 1, 2019 and December 31, 2020.

Related: interval.

case

Tests an expression against a list of cases and returns the corresponding value of the first matching case, with an optional default value if nothing else is met.

Syntax: case(condition, output, …)

Example: case([Weight] > 200, "Large", [Weight] > 150, "Medium", "Small") If a Weight is 250, the expression would return "Large". In this case, the default value is "Small", so any Weight 150 or less would return "Small".

ceil

Rounds a decimal up (ciel as in ceiling).

Syntax: ceil(column).

Example: ceil([Price]). ceil(2.99) would return 3.

Related: floor, round.

coalesce

Looks at the values in each argument in order and returns the first non-null value for each row.

Syntax: coalesce(value1, value2, …)

Example: coalesce([Comments], [Notes], "No comments"). If both the Comments and Notes columns are null for that row, the expression will return the string "No comments".

concat

Combine two or more strings together.

Syntax: concat(value1, value2, …)

Example: concat([Last Name], ", ", [First Name]) would produce a string of the format "Last Name, First Name", like "Palazzo, Enrico".

contains

Checks to see if string1 contains string2 within it.

Syntax: contains(string1, string2)

Example: contains([Status], "Class"). If Status were "Classified", the expression would return true.

Related: regexextract.

endswith

Returns true if the end of the text matches the comparison text.

Syntax: endsWith(text, comparison)

endsWith([Appetite], "hungry")

Related: contains and startswith.

exp

Returns Euler's number, e, raised to the power of the supplied number. (Euler sounds like "Oy-ler").

Syntax: exp(column).

Example: exp([Interest Months])

Related: power.

floor

Rounds a decimal number down.

Syntax: floor(column)

Example: floor([Price]). If the Price were 1.99, the expression would return 1.

Related: ceil, round.

interval

Checks a date column's values to see if they're within the relative range.

Syntax: interval(column, number, text).

Example: interval([Created At], -1, "month").

Related: between.

isempty

Returns true if the column is empty.

Syntax: isempty(column)

Example: isempty([Discount]) would return true if there were no value in the discount field.

isnull

Returns true if the column is null.

Syntax: isnull(column)

Example: isnull([Tax]) would return true if no value were present in the column for that row.

lefttrim

Removes leading whitespace from a string of text.

Syntax: ltrim(text)

Example: ltrim([Comment]). If the comment were " I'd prefer not to", ltrim would return "I'd prefer not to".

Related: trim and righttrim.

length

Returns the number of characters in text.

Syntax: length(text)

Example: length([Comment]) If the comment were "wizard", length would return 6 ("wizard" has six characters).

log

Returns the base 10 log of the number.

Syntax: log(column).

Example: log([Value]).

lower

Returns the string of text in all lower case.

Syntax: lower(text).

Example: lower([Status]). If the Status were "QUIET", the expression would return "quiet".

Related: upper.

power

Raises a number to the power of the exponent value.

Syntax: power(column, exponent).

Example: power([Length], 2). If the length were 3, the expression would return 9 (3 to the second power is 3*3).

Databases that don't support power: SQLite.

Related: exp.

regexextract

Extracts matching substrings according to a regular expression.

Syntax: regexextract(text, regular_expression).

Example: regexextract([Address], "[0-9]+").

Databases that don't support regexextract: H2, SQL Server, SQLite.

Related: contains.

replace

Replaces a part of the input text with new text.

Syntax: replace(text, find, replace).

Example: replace([Title], "Enormous", "Gigantic").

righttrim

Removes trailing whitespace from a string of text.

Syntax: rtrim(text)

Example: rtrim([Comment]). If the comment were "Fear is the mindkiller. ", the expression would return "Fear is the mindkiller."

Related: trim and lefttrim.

round

Rounds a decimal number either up or down to the nearest integer value.

Syntax: round(column).

Example: round([Temperature]). If the temp were 13.5 degrees centigrade, the expression would return 14.

sqrt

Returns the square root of a value.

Syntax: sqrt(column).

Example: sqrt([Hypotenuse]).

Databases that don't support sqrt: SQLite.

Related: Power.

startswith

Returns true if the beginning of the text matches the comparison text.

Syntax: startsWith(text, comparison).

Example: startsWith([Course Name], "Computer Science") would return true for course names that began with "Computer Science", like "Computer Science 101: An introduction".

Related: endswith, contains.

substring

Returns a portion of the supplied text, specified by a starting position and a length.

Syntax: substring(text, position, length)

Example: substring([Title], 0, 10) returns the first 11 letters of a string (the string index starts at position 0).

Related: replace.

trim

Removes leading and trailing whitespace from a string of text.

Syntax: trim(text)

Example: trim([Comment]) will remove any whitespace characters on either side of a comment.

upper

Returns the text in all upper case.

Syntax: upper(text).

Example: upper([Status]). If status were "hyper", upper("hyper") would return "HYPER".

Database limitations

Limitations are noted for each aggregation and function above, and here there are in summary:

H2: Median, Percentile and regexextract

MySQL/MariaDB: Median, Percentile.

SQL Server: Median, Percentile and regexextract

SQLite: log, Median, Percentile, power, regexextract, StandardDeviation, sqrt and Variance

Vertica: Median and Percentile

Additionally, Presto only provides approximate results for Median and Percentile.

If you're using or maintaining a third-party database driver, please refer to the wiki to see how your driver might be impacted.

See Custom expressions in the notebook editor to learn more.