Functions

Functions grouped by what they work on.

Text

Case and length

Name	What it does
LOWER	A string in lower case.
UPPER	A string in upper case.
INITCAP	A string with the first letter of each word in upper case.
LENGTH	The number of characters in a string.

Find

Name	What it does
STRPOS	The position of a substring in a string.
POSITION	The position of a substring in a string, standard form.
STARTS_WITH	Whether a string begins with a prefix.
REGEXP_LIKE	Whether a string matches a regular expression.

Take part of a string

Name	What it does
SUBSTRING	Part of a string, by start position and length.
LEFT	The first characters of a string.
RIGHT	The last characters of a string.
SPLIT_PART	One part of a string split on a delimiter.

Change a string

Name	What it does
REPLACE	A string with every occurrence of one substring replaced by another.
REGEXP_REPLACE	A string with matches of a regular expression replaced.
TRIM	A string with characters removed from both ends.
LTRIM	A string with characters removed from the start.
RTRIM	A string with characters removed from the end.
LPAD	A string padded on the left to a length.
RPAD	A string padded on the right to a length.
REVERSE	A string with its characters in reverse order.
CONCAT	Strings joined end to end, skipping NULLs.
CONCAT_WS	Strings joined with a separator, skipping NULLs.

Split into rows

Name	What it does
STRING_TO_TABLE	One row for each piece of a string split on a delimiter.
REGEXP_SPLIT_TO_TABLE	One row for each piece of a string split on a regular expression.

Number

Name	What it does
ABS	The absolute value of a number.
ROUND	A number rounded to a number of decimal places.
TRUNC	A number cut to a number of decimal places, toward zero.
CEIL	The smallest whole number not less than a number.
FLOOR	The largest whole number not greater than a number.
SIGN	-1, 0 or 1 by the sign of a number.
MOD	The remainder of one number divided by another.
POWER	A number raised to a power.
SQRT	The square root of a number.

Date and time

Name	What it does
CURRENT_DATE	Today's date in UTC.
EXTRACT	One field of a date or timestamp; with epoch, the way to do date arithmetic.
DATE_PART	One field of a date or timestamp, such as the year, month or epoch seconds.
DATE_TRUNC	A date or timestamp cut back to the start of its year, month, week or other unit.

Conditional

Name	What it does
COALESCE	The first argument that is not NULL.
NULLIF	NULL when two values are equal, otherwise the first value.
GREATEST	The largest of its arguments, ignoring NULLs.
LEAST	The smallest of its arguments, ignoring NULLs.

Aggregate

Name	What it does
COUNT	The number of rows, non-NULL values or distinct values in a group.
SUM	The total of the non-NULL values in a group.
AVG	The mean of the non-NULL values in a group.
MIN	The smallest non-NULL value in a group.
MAX	The largest non-NULL value in a group.
STRING_AGG	The values of a group joined into one string with a separator.
ARRAY_AGG	The values of a group collected into a list.

Window

Ranking

Name	What it does
ROW_NUMBER	The position of each row in its partition, counting from 1.
RANK	The rank of each row in its partition, with gaps after ties.
DENSE_RANK	The rank of each row in its partition, with no gaps after ties.
NTILE	Splits each partition into a number of buckets of near-equal size.
PERCENT_RANK	The relative rank of each row in its partition, from 0 to 1.
CUME_DIST	The share of rows in the partition that sort at or before each row.

Other rows

Name	What it does
LAG	A value from a row before the current one in the partition.
LEAD	A value from a row after the current one in the partition.
FIRST_VALUE	The value from the first row of the window frame.
LAST_VALUE	The value from the last row of the window frame.
NTH_VALUE	The value from the nth row of the window frame.