SQL Functions

AVG

AVG(expression)

Returns the average of a given numeric expression.

COUNT

SELECT COUNT(column_name) FROM schema.table WHERE condition

Returns the number records that match the given criteria. Nulls are not counted.

GROUP_CONCAT

GROUP_CONCAT(expression)

Returns a string with concatenated values that are comma separated and that are non-null from a group. Will return null when there are non-null values.

MAX

SELECT MAX(column_name) FROM schema.table WHERE condition

Returns largest value in a specified column.

MIN

SELECT MIN(column_name) FROM schema.table WHERE condition

Returns smallest value in a specified column.

SUM

SUM(column_name)

Returns the sum of the numeric values provided.

ARRAY*

ARRAY(expression)

Returns a list of data as a field.

DISTINCT_ARRAY*

DISTINCT_ARRAY(expression)

When placed around a standard ARRAY() function, returns a distinct (deduplicated) results set.

CAST

CAST(expression AS datatype(length))

Converts a value to a specified datatype.

CONVERT

CONVERT(data_type(length), expression, style)

Converts a value from one datatype to a different, specified datatype.

CURRENT_DATE

CURRENT_DATE()

Returns the current date in UTC in “YYYY-MM-DD” String format.

CURRENT_TIME

CURRENT_TIME()

Returns the current time in UTC in “HH:mm:ss.SSS” string format.

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP

Referencing this variable will evaluate as the current Unix Timestamp in milliseconds. For more information, go here.

DATE

DATE([date_string])

Formats and returns the date_string argument in UTC in ‘YYYY-MM-DDTHH:mm:ss.SSSZZ’ string format. If a date_string is not provided, the function will return the current UTC date/time value in the return format defined above. For more information, go here.

DATE_ADD

DATE_ADD(date, value, interval)

Adds the defined amount of time to the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted interval values: Either string value (key or shorthand) can be passed as the interval argument. For more information, go here.

DATE_DIFF

DATEDIFF(date_1, date_2[, interval])

Returns the difference between the two date values passed based on the interval as a Number. If an interval is not provided, the function will return the difference value in milliseconds. For more information, go here.

DATE_FORMAT

DATE_FORMAT(date, format)

Formats and returns a date value in the String format provided. Find more details on accepted format values in the moment.js docs. For more information, go here.

DATE_SUB

DATE_SUB(date, format)

Subtracts the defined amount of time from the date provided in UTC and returns the resulting Unix Timestamp in milliseconds. Accepted date_sub interval values- Either string value (key or shorthand) can be passed as the interval argument. For more information, go here.

DAY

DAY(date)

Return the day of the month for the given date.

DAYOFWEEK

DAYOFWEEK(date)

Returns the numeric value of the weekday of the date given(“YYYY-MM-DD”).NOTE: 0=Sunday, 1=Monday, 2=Tuesday, 3=Wednesday, 4=Thursday, 5=Friday, and 6=Saturday.

EXTRACT

EXTRACT(date, date_part)

Extracts and returns the date_part requested as a String value. Accepted date_part values below show value returned for date = “2020-03-26T15:13:02.041+000” For more information, go here.

GETDATE

GETDATE()

Returns the current Unix Timestamp in milliseconds.

GET_SERVER_TIME

GET_SERVER_TIME()

Returns the current date/time value based on the server’s timezone in YYYY-MM-DDTHH:mm:ss.SSSZZ String format.

OFFSET_UTC

OFFSET_UTC(date, offset)

Returns the UTC date time value with the offset provided included in the return String value formatted as YYYY-MM-DDTHH:mm:ss.SSSZZ. The offset argument will be added as minutes unless the value is less than 16 and greater than -16, in which case it will be treated as hours.

NOW

NOW()

Returns the current Unix Timestamp in milliseconds.

HOUR

HOUR(datetime)

Returns the hour part of a given date in range of 0 to 838.

MINUTE

MINUTE(datetime)

Returns the minute part of a time/datetime in range of 0 to 59.

MONTH

MONTH(date)

Returns month part for a specified date in range of 1 to 12.

SECOND

SECOND(datetime)

Returns the seconds part of a time/datetime in range of 0 to 59.

YEAR

YEAR(date)

Returns the year part for a specified date.

IF

IF(condition, value_if_true, value_if_false)

Returns a value if the condition is true, or another value if the condition is false.

IIF

IIF(condition, value_if_true, value_if_false)

Returns a value if the condition is true, or another value if the condition is false.

IFNULL

IFNULL(expression, alt_value)

Returns a specified value if the expression is null.

NULLIF

NULLIF(expression_1, expression_2)

Returns null if expression_1 is equal to expression_2, if not equal, returns expression_1.

ABS

ABS(expression)

Returns the absolute value of a given numeric expression.

CEIL

CEIL(number)

Returns integer ceiling, the smallest integer value that is bigger than or equal to a given number.

EXP

EXP(number)

Returns e to the power of a specified number.

FLOOR

FLOOR(number)

Returns the largest integer value that is smaller than, or equal to, a given number.

RANDOM

RANDOM(seed)

Returns a pseudo random number.

ROUND

ROUND(number,decimal_places)

Rounds a given number to a specified number of decimal places.

SQRT

SQRT(expression)

Returns the square root of an expression.

CONCAT

CONCAT(string_1, string_2, ...., string_n)

Concatenates, or joins, two or more strings together, resulting in a single string.

CONCAT_WS

CONCAT_WS(separator, string_1, string_2, ...., string_n)

Concatenates, or joins, two or more strings together with a separator, resulting in a single string.

INSTR

INSTR(string_1, string_2)

Returns the first position, as an integer, of string_2 within string_1.

LEN

LEN(string)

Returns the length of a string.

LOWER

LOWER(string)

Converts a string to lower-case.

REGEXP

SELECT column_name FROM schema.table WHERE column_name REGEXP pattern

Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null.

REGEXP_LIKE

SELECT column_name FROM schema.table WHERE REGEXP_LIKE(column_name, pattern)

Searches column for matching string against a given regular expression pattern, provided as a string, and returns all matches. If no matches are found, it returns null.

REPLACE

REPLACE(string, old_string, new_string)

Replaces all instances of old_string within new_string, with string.

SUBSTRING

SUBSTRING(string, string_position, length_of_substring)

Extracts a specified amount of characters from a string.

TRIM

TRIM([character(s) FROM] string)

Removes leading and trailing spaces, or specified character(s), from a string.

UPPER

UPPER(string)

Converts a string to upper-case.

BETWEEN

SELECT column_name(s) FROM schema.table WHERE column_name BETWEEN value_1 AND value_2

(inclusive) Returns values(numbers, text, or dates) within a given range.

IN

SELECT column_name(s) FROM schema.table WHERE column_name IN(value(s))

Used to specify multiple values in a WHERE clause.

LIKE

SELECT column_name(s) FROM schema.table WHERE column_n LIKE pattern

Searches for a specified pattern within a WHERE clause.

DISTINCT

SELECT DISTINCT column_name(s) FROM schema.table

Returns only unique values, eliminating duplicate records.

FROM

FROM schema.table

Used to list the schema(s), table(s), and any joins required for a SQL statement.

GROUP BY

SELECT column_name(s) FROM schema.table WHERE condition GROUP BY column_name(s) ORDER BY column_name(s)

Groups rows that have the same values into summary rows.

HAVING

SELECT column_name(s) FROM schema.table WHERE condition GROUP BY column_name(s) HAVING condition ORDER BY column_name(s)

Filters data based on a group or aggregate function.

SELECT

SELECT column_name(s) FROM schema.table

Selects data from table.

WHERE

SELECT column_name(s) FROM schema.table WHERE condition

Extracts records based on a defined condition.

CROSS JOIN

SELECT column_name(s) FROM schema.table_1 CROSS JOIN schema.table_2

Returns a paired combination of each row from table_1 with row from table_2. Note: CROSS JOIN can return very large result sets and is generally considered bad practice.

FULL OUTER

SELECT column_name(s) FROM schema.table_1 FULL OUTER JOIN schema.table_2 ON table_1.column_name = table_2.column_name WHERE condition

Returns all records when there is a match in either table_1 (left table) or table_2 (right table).

[INNER] JOIN

SELECT column_name(s) FROM schema.table_1 INNER JOIN schema.table_2 ON table_1.column_name = table_2.column_name

Return only matching records from table_1 (left table) and table_2 (right table). The INNER keyword is optional and does not affect the result.

LEFT [OUTER] JOIN

SELECT column_name(s) FROM schema.table_1 LEFT OUTER JOIN schema.table_2 ON table_1.column_name = table_2.column_name

Return all records from table_1 (left table) and matching data from table_2 (right table). The OUTER keyword is optional and does not affect the result.

RIGHT [OUTER] JOIN

SELECT column_name(s) FROM schema.table_1 RIGHT OUTER JOIN schema.table_2 ON table_1.column_name = table_2.column_name

Return all records from table_2 (right table) and matching data from table_1 (left table). The OUTER keyword is optional and does not affect the result.

IS NOT NULL

SELECT column_name(s) FROM schema.table WHERE column_name IS NOT NULL

Tests for non-null values.

IS NULL

SELECT column_name(s) FROM schema.table WHERE column_name IS NULL

Tests for null values.

DELETE

DELETE FROM schema.table WHERE condition

Deletes existing data from a table.

INSERT

INSERT INTO schema.table(column_name(s)) VALUES(value(s))

Inserts new records into a table.

UPDATE

UPDATE schema.table SET column_1 = value_1, column_2 = value_2, ...., WHERE condition

Alters existing records in a table.