SQL Functions
A reference for the SQL functions you'll use every day, each with interactive, runnable examples.
Numeric
ABS()
numericReturns the absolute (non-negative) value of a number.
CEIL()
numericRounds a number up to the nearest integer (ceiling).
FLOOR()
numericRounds a number down to the nearest integer (floor).
GREATEST()
numericReturns the largest value from a list of expressions.
LEAST()
numericReturns the smallest value from a list of expressions.
MOD()
numericReturns the remainder of a division (modulo).
POWER()
numericRaises a base to a specified exponent.
ROUND()
numericRounds a numeric value to a specified number of decimal places.
SIGN()
numericReturns -1, 0, or 1 indicating the sign of a number.
SQRT()
numericReturns the square root of a non-negative number.
TRUNC()
numericTruncates a number toward zero, removing digits past a given precision.
Aggregate
ARRAY_AGG()
aggregateCollects values from a group into a single array (list).
AVG()
aggregateCalculates the average (mean) of the values in a numeric column across a group of rows.
BOOL_AND()
aggregateReturns TRUE if every value in the group is TRUE (logical AND across rows).
BOOL_OR()
aggregateReturns TRUE if at least one value in the group is TRUE (logical OR across rows).
COUNT()
aggregateReturns the number of rows that match a query, optionally counting only non-null values in a specific column.
MAX()
aggregateReturns the largest value in a column across a group of rows.
MIN()
aggregateReturns the smallest value in a column across a group of rows.
STDDEV()
aggregateReturns the standard deviation of a set of values, measuring spread around the mean.
STRING_AGG()
aggregateConcatenates values from a group into a single string, separated by a delimiter.
SUM()
aggregateAdds up all the values in a numeric column across a group of rows.
VARIANCE()
aggregateReturns the variance (squared spread) of a set of numeric values.
Conversion
CAST()
conversionConverts a value from one data type to another, such as a number to text or text to a number.
COALESCE()
conversionReturns the first non-null value from a list of expressions.
NULLIF()
conversionReturns NULL if two expressions are equal; otherwise returns the first.
TO_CHAR()
conversionFormats a date, number, or timestamp as a string using a format pattern.
TO_DATE()
conversionParses a string into a DATE using a format pattern.
String
CONCAT()
stringJoins two or more strings together into a single string.
LEFT()
stringReturns the first N characters from the left of a string.
LENGTH()
stringReturns the number of characters in a string.
LOWER()
stringConverts all characters in a string to lowercase.
LTRIM()
stringRemoves leading (left-side) characters, usually spaces, from a string.
POSITION()
stringReturns the 1-based location of a substring within a string.
REPEAT()
stringConcatenates a string to itself a given number of times.
REPLACE()
stringReplaces all occurrences of a substring within a string.
REVERSE()
stringReturns the characters of a string in reverse order.
RIGHT()
stringReturns the last N characters from the right of a string.
RTRIM()
stringRemoves trailing (right-side) characters, usually spaces, from a string.
SPLIT_PART()
stringSplits a string on a delimiter and returns the Nth piece.
SUBSTRING()
stringExtracts a portion of a string, starting at a given position and running for a given length.
TRIM()
stringRemoves leading and/or trailing characters (usually spaces) from a string.
UPPER()
stringConverts all characters in a string to uppercase.
Date & Time
CURRENT_DATE()
dateReturns today's date (no time component).
CURRENT_TIMESTAMP()
dateReturns the current date and time (with time zone in some engines).
DATE_ADD()
dateAdds an interval to a date or timestamp.
DATE_SUB()
dateSubtracts an interval from a date or timestamp.
DATE_TRUNC()
dateTruncates a date/timestamp down to the start of a given unit.
DATEDIFF()
dateReturns the difference between two dates in a specified unit.
EXTRACT()
datePulls a single date part (year, month, day, ...) out of a date or timestamp.
NOW()
dateReturns the current date and time.