SQL Functions Library

COUNT()

Counts rows or non-NULL values in a group.

SUM()

Adds up the numeric values in a group.

AVG()

Returns the mean of the numeric values in a group.

MIN()

Returns the smallest value in a group.

MAX()

Returns the largest value in a group.

STRING_AGG()

Concatenates values across rows into one delimited string.

ROW_NUMBER()

Assigns a unique sequential number to each row in a partition.

RANK()

Ranks rows within a partition, leaving gaps after ties.

DENSE_RANK()

Ranks rows within a partition with no gaps after ties.

LAG()

Returns a value from a previous row in the window.

LEAD()

Returns a value from a following row in the window.

NTILE()

Splits ordered rows into N roughly equal buckets.

FIRST_VALUE()

Returns the first value in the window frame.

LAST_VALUE()

Returns the last value in the window frame.

NTH_VALUE()

Returns the Nth value in the window frame.

CONCAT()

Joins two or more strings into one.

CONCAT_WS()

Joins strings with a separator, skipping NULLs.

SUBSTRING()

Extracts part of a string by position and length.

LENGTH()

Returns the length of a string.

UPPER()

Converts a string to uppercase.

LOWER()

Converts a string to lowercase.

TRIM()

Removes leading and trailing spaces or characters.

REPLACE()

Replaces every occurrence of a substring.

LEFT()

Returns the leftmost N characters.

RIGHT()

Returns the rightmost N characters.

POSITION()

Finds the index of a substring within a string.

REVERSE()

Reverses the characters in a string.

LPAD()

Left-pads a string to a target length.

RPAD()

Right-pads a string to a target length.

DATEADD()

Adds an interval to a date (DATE_ADD in MySQL).

DATEDIFF()

Returns the difference between two dates.

NOW()

Returns the current date and time.

CURRENT_DATE

Returns today's date with no time.

EXTRACT()

Pulls a component such as year or month from a date.

DATE_TRUNC()

Truncates a timestamp down to a unit.

DATE_FORMAT()

Formats a date or time as a string.

ROUND()

Rounds a number to a given number of decimals.

CEIL()

Rounds a number up to the nearest integer.

FLOOR()

Rounds a number down to the nearest integer.

ABS()

Returns the absolute value of a number.

MOD()

Returns the remainder of a division.

POWER()

Raises a number to an exponent.

SQRT()

Returns the square root of a number.

TRUNCATE()

Cuts off decimals without rounding.

RANDOM()

Returns a random number (RAND in MySQL/SQL Server).

COALESCE()

Returns the first non-NULL value from a list.

CASE

Returns a value based on conditional logic.

NULLIF()

Returns NULL if two values are equal, else the first.

IFNULL()

Replaces NULL with a fallback value (two arguments).

GREATEST()

Returns the largest of several values in a row.

LEAST()

Returns the smallest of several values in a row.

CAST()

Converts a value from one data type to another.

CONVERT()

Converts a value to another type (SQL Server, MySQL).

JSON_EXTRACT()

Extracts a value from a JSON document by path.