APPROXIMATE COUNT (DISTINCT)

Returns the approximate count of a number of unique values in a column.

APPROXIMATE_COUNT_DISTINCT(X)

CORR

Returns the correlation coefficient of independent variable x and dependent variable y. It is computed for non-null pairs of values only.

For non-null data, it is equivalent to: COVAR_POP(y, x) / (STDDEV_POP(x) * STDDEV_POP(y)).

It is an aggregate function.

VAR_POP() returns NULL if there were no matching rows.

CORR(x,y)

COUNT

Returns a count of the number of non-NULL values of expr. The result is an integer value. It is an aggregate function.

COUNT(*) counts the total number of rows.

COUNT() returns 0 if there were no matching rows.

COUNT(expr)

COVAR_POP

Returns the population covariance of independent variable x and dependent variable y. It is computed for non-null pairs of values only.

For non-null data, it is equivalent to: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / COUNT(*).

It is an aggregate function.

VAR_POP() returns NULL if there were no matching rows.

COVAR_POP(x,y)

COVAR_SAMP

Returns the sample covariance of independent variable x and dependent variable y; the denominator is the sample size minus one. It is computed for non-null pairs of values only.

For non-null data, it is equivalent to: (SUM(x*y) - SUM(x) * SUM(y) / COUNT(*)) / (COUNT(*) - 1).

It is an aggregate function.

VAR_POP() returns NULL if there were no matching rows.

COVAR_SAMP(x,y)

FIRST_NON_NULL_VALUE

Returns the first non-null value in expr, or NULL if no non-NULL values were found. It is an aggregate function.

FIRST_NON_NULL_VALUE(expr)

FIRST_VALUE

Returns the first value in expr, including NULL. It is an aggregate function.

FIRST_VALUE(expr)

LAST_NON_NULL_VALUE

Returns the last non-null value in expr, or NULL if no non-NULL values were found. It is an aggregate function.

LAST_NON_NULL_VALUE(expr)

LAST_VALUE

Returns the last value in expr, including NULL. It is an aggregate function.

LAST_VALUE(expr)

MAX

Returns the largest, or maximum, value of expr. MAX() can also take a string argument in which case it returns the maximum string value.

It is an aggregate function.

MAX() returns NULL if there were no matching rows.

MAX(expr)

MEDIAN

Returns the estimated median value of expr. It is an aggregate function.

MEDIAN(expr)

MIN

Returns the smallest, or minimum, value of expr. MIN() can also take a string argument in which case it returns the minimum string value.

It is an aggregate function.

MIN() returns NULL if there were no matching rows.

MIN(expr)

PERCENTILE

Returns the ntile percentile of expr. It is an aggregate function.

PERCENTILE(expr, ntile)

STD

STD() is a synonym for STDDEV_SAMP().

STD(expr)

STDDEV

STDDEV() is a synonym for STDDEV_SAMP().

STDDEV(expr)

STDDEV_POP

Returns the population standard deviation of expr (the square root of VAR_POP()).

It is an aggregate function.

STDDEV_POP() returns NULL if there were no matching rows.

STDDEV_POP(expr)

STDDEV_SAMP

Returns the sample standard deviation of expr (the square root of VAR_SAMP()).

It is an aggregate function.

STDDEV_POP() returns NULL if there were no matching rows.

STDDEV_SAMP(expr)

SUM

Returns the sum of expr. If the return set has no rows, SUM() returns NULL.

It is an aggregate function.

SUM(expr)

VAR_POP

Returns the population standard variance of expr. It considers rows as the whole population, not as a sample, so it has the number of rows as the denominator.

Variance is calculated by working out the mean for the set for each number, subtracting the mean and squaring the result, and finally calculating the average of the resulting differences

It is an aggregate function.

VAR_POP() returns NULL if there were no matching rows.

VAR_POP(expr)

VAR_SAMP

Returns the sample standard variance of expr. The denominator is the number of rows minus one.

Variance is calculated by working out the mean for the set for each number, subtracting the mean and squaring the result, and finally calculating the average of the resulting differences.

It is an aggregate function.

VAR_POP() returns NULL if there were no matching rows.

VAR_SAMP(expr)

VARIANCE

VARIANCE() is a synonym for VAR_SAMP().

VARIANCE(expr)

VARIANCE_POP

VARIANCE_POP() is a synonym for VAR_POP().

VARIANCE_POP(expr)

VARIANCE_SAMP

VARIANCE_SAMP() is a synonym for VAR_SAMP().

VARIANCE_SAMP(expr)

ABS

Returns the absolute (non-negative) value of X. If X is not a number, it is converted to a numeric type.

ABS(X)

ACOS

Returns the arc cosine of X, that is, the value whose cosine is X. Returns NaN if X is not in the range -1 to 1.

ACOS(X)

ASIN

Returns the arc sine of X, that is, the value whose sine is X. Returns NaN if X is not in the range -1 to 1.

ASIN(X)

ATAN

Returns the arc tangent of X, that is, the value whose tangent is X.

ATAN(X)

ATAN2

Returns the arc tangent of the two variables X and Y. It is similar to calculating the arc tangent of Y / X, except that the signs of both arguments are used to determine the quadrant of the result.

ATAN2(Y,X)

AVG

Returns the average value of expr. NULL values are ignored. It is an aggregate function.

AVG() returns NULL if there were no matching rows.

AVG(expr)

CBRT

Returns the cube root of X. If X is negative, NaN is returned.

CBRT(X)

CEIL

Returns the smallest integer value not less than X. Alias for CEILING().

CEIL(X)

CEILING

Returns the smallest integer value not less than X.

CEILING(X)

CONV

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base.

CONV(N,from_base,to_base)

CONVERT_UNIT

Converts expr from from_unit to to_unit. expr will be converted to type DOUBLE if it is not already, and the result always has type DOUBLE.

from_unit and to_unit are strings describing conmensurable units in English, e.g. ''cubic feet'' and ''liters'', or ''mph'' and ''m/s''. Unit strings follow these rules:

1. Most SI (Metric) and Imperial units are supported. Imperial units are defined in terms of their SI counterparts. Imperial units of length, area, and mass/weight are mostly agreed upon internationally; the ton is an exception. Units of volume, like the gallon, also differ between the UK and the USA. When such differences arise, USA customary values (e.g. the short ton; 2000 avoirdupois pounds) are used.

2. Units may be written out in full, and optionally pluralized. Metric units may have metric prefixes, and computing units may have metric or binary prefixes. Examples: ''feet'', ''year'', ''kilowatt'', ''megabyte'', ''mebibyte''

3. Units may be abbreviated. Metric and binary prefixes on abbreviated units must also be abbreviated. Abbreviated units are case-sensitive. Examples: ''mi'' (miles), ''KiBps'' (kibibytes per second), ''uPa'' (micropascals)

4. Units may be exponentiated using the caret ''^'' or by ending a unit's name with a number. Exponents may be negative to indicate inversion of the unit. Units may also be prefixed with ''cu''/''cubic'' or ''sq''/''square'', followed by a space '' '' or hyphen ''-'', which are equivalent to the exponents ''3'' and ''2'', respectively. Examples: ''sq ft'' (square feet), ''m3'' (cubic meters), ''s^-1'' (inverse seconds; hertz)

5. Units may be divided using the forward slash ''/'' character or the word '' per ''. Examples: ''miles per hour'', ''metres/second/second'' (meters per second squared; acceleration)

6. Units may be multiplied using the asterisk ''*'' or by being combined with a hyphen ''-'' or space. The asterisk follows normal multiplication order of operations, while the hyphen or space form have the highest precedence, as though the combined units were a single unit name. Examples: ''N*m'' (newton meters; joules), ''half-gal'' (half gallons), ''kilogram-meters/second2'' (kilogram meters per second squared; newtons)

7. Parentheses may be used to affect the order of operations of exponentiation, multiplication, and division. Examples: ''(m/s)^2'' (meters squared per second squared; acceleration of area)

8. Units whose names or abbreviations consist of more than one word can be written with an underscore ''_'' separator or simply without the space. Examples: ''fluid_ounce'', ''floz'' (fluid ounce), ''poundforce'', ''astronomical_unit''

Units must be conmensurable to be converted; they must have the same dimensions. There are five supported dimensional values: Time, length, mass, electric current, and information. Units may have a positive, negative, or zero number of dimensions in any of these values. Some units have zero dimensions in all categories; these units are raw quantities like ''dozen'' or ''half''. ''meters / second'' has one positive length dimension, and one negative time dimension; it is a unit of velocity. The ''gallon'' has three positive length dimensions; it is a unit of volume. These can be converted to ''miles / hour'' and ''cubic inches'', respectively, because they have the same dimensions. Attempting to convert between units that have different dimensions, like from ''seconds'' to ''miles'', results in an error.

Note that due to the nature of DOUBLE arithmetic, results may appear inexact. The ROUND() function may be used to correct results in these cases.

CONVERT_UNIT(expr,from_unit,to_unit)

COS

Returns the cosine of X, where X is given in radians.

COS(X)

COSH

Returns the hyperbolic cosine of X.

COSH(X)

COT

Returns the cotangent of X.

COT(X)

CRC32

Computes a cyclic redundancy check value and returns a 32-bit unsigned value. The result is NULL if the argument is NULL. The argument is expected to be a string and (if possible) is treated as one if it is not.

CRC32(expr)

DECIMAL

Returns the value of expr converted to a fixed-precision DECIMAL type.

DECIMAL(expr), CAST(expr AS DECIMAL)

DEGREES

Returns the argument X, converted from radians to degrees.

This is the converse of the RADIANS() function.

DEGREES(X)

DISTANCE

Returns the kilometer distance between two points on the WGS-84 model of Earth, given as degrees longitude and latitude. Vincenty's formulae are used to calculate the distance.

DISTANCE(lat0, lon0, lat1, lon1)

DISTANCEV

DISTANCEV() is a synonym for DISTANCE(). Both use the slower but more accurate Vincenty method.

DISTANCEV(lat0, lon0, lat1, lon1)

DOUBLE

Returns the value of expr converted to a floating-point DOUBLE type.

DOUBLE(expr), CAST(expr AS DOUBLE)

E

Returns the value of e (Euler's number) as a double-precision floating point.

E()

EXP

Returns the value of e (the base of natural logarithms) raised to the power of X. The inverse of this function is LOG() (using a single argument only) or LN().

If X is NULL, this function returns NULL.

EXP(X)

FINITE

Returns TRUE if expr is a finite number, NULL if it is NULL, or FALSE if it is NaN, Infinity, or -Infinity.

FINITE(expr)

FLOOR

Returns the largest integer value not greater than X.

FLOOR(X)

INFINITY

Returns the floating point representation of infinity.

INF(), INFINITY()

LN

Returns the natural logarithm of X; that is, the base-e logarithm of X. If X is less than or equal to 0, or NULL, then NULL is returned.

The inverse of this function is EXP().

LN(X)

LOG

If called with one parameter, this function returns the natural logarithm of X. If X is less than or equal to 0, then NULL is returned.

If called with two parameters, it returns the logarithm of X to the base B. If B is <= 1 or X <= 0, the function returns NULL.

If any argument is NULL, the function returns NULL.

The inverse of this function (when called with a single argument) is the EXP() function.

LOG(X), LOG(B,X)

LOG10

Returns the base-10 logarithm of X.

LOG10(X)

LOG2

Returns the base-2 logarithm of X.

LOG2(X)

LONG

Returns the value of expr converted to a LONG type integer.

LONG(expr), CAST(expr AS LONG)

LONG_MAX

Returns the value -(2^63) as an integer. This is the smallest value that the type LONG can represent.

LONG_MAX()

LONG_MIN

Returns the value 2^63 - 1 as an integer. This is the largest value that the type LONG can represent.

LONG_MIN()

MOD

Modulo operation. Returns the remainder of N divided by M.

MOD(N,M), N % M

NAN

Returns the floating point representation of NaN (not-a-number).

NAN()

NEGATIVE_INFINITY

Returns the floating point representation of negative infinity.

NEGINF(), NEGATIVE_INFINITY()

PI

Returns the value of π (pi) as a double-precision floating point.

PI()

POW

Returns the value of X raised to the power of Y.

POW(X,Y)

POWER

This is a synonym for POW(), which returns the value of X raised to the power of Y.

POWER(X,Y)

RADIANS

Returns the argument X, converted from degrees to radians. Note that π radians equals 180 degrees.

This is the converse of the DEGREES() function.

RADIANS(X)

RAND

Returns a random DOUBLE precision floating point value v in the range 0 <= v < 1.0. If a constant integer argument N is specified, it is used as the seed value, which produces a repeatable sequence of column values.

RAND(), RAND(N)

ROUND

Rounds the argument X to D decimal places. D defaults to 0 if not specified.

ROUND(X), ROUND(X,D)

RPAD

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

RPAD(str, len, padstr)

RTRIM

Returns the string str with trailing space characters removed.

RTRIM(str)

SIGN

Returns the sign of the argument as -1, 0, or 1, depending on whether X is negative, zero, or positive.

SIGN(X)

SIN

Returns the sine of X, where X is given in radians.

SIN(X)

SINH

Returns the hyperbolic sine of X.

SINH(X)

SQRT

Returns the square root of X. If X is negative, NaN is returned.

SQRT(X)

TAN

Returns the tangent of X, where X is given in radians.

TAN(X)

TANH

Returns the hyperbolic tangent of X.

TANH(X)

TRUNCATE

Returns the number X, truncated to D decimal places. If D is 0, the result has no decimal point or fractional part.

TRUNCATE(X,D)

WIDTH_BUCKET

Divides the range [min, max) into equal-width buckets and returns the number of the bucket into which expr falls. Buckets are numbered from 1 to N, where N is the number of buckets. Values that fall outside the range are put in overflow buckets with numbers 0 and N + 1.

Be aware that bucket ranges are inclusive of their minimum bound and exclusive of their maximum bound. So WIDTH_BUCKET(100, 0, 100, 4) = 5, the overflow bucket. Also note that min may be less than max, in which case bucket minimums are larger than bucket maximums. So WIDTH_BUCKET(100, 100, 0, 4) = 1, the first bucket.

WIDTH_BUCKET(expr, min, max, bucket_count)

ANOVAF

Returns the F-value of a one-way Analysis of Variance of the samples. At least two samples must be provided, and each sample must have at least two non-null values. Note that a one-way ANOVA on two samples is equivalent to the Student's t-test (see the TTEST function).

See also: ANOVAP()

ANOVAF(sample1,sample2[,sample3[,...,sampleN]])

ANOVAP

Returns the p-value of a one-way Analysis of Variance of the samples. At least two samples must be provided, and each sample must have at least two non-null values. Note that a one-way ANOVA on two samples is equivalent to the Student's t-test (see the TTEST function).

See also: ANOVAF()

ANOVAP(sample1,sample2[,sample3[,...,sampleN]])

TTEST

Returns the p-value result of a Student's t-test. Arguments sample1 and sample2 are two numeric columns with at least 2 non-null values each. The tails argument is either 1 or 2, indicating a one- or two-tailed test. The type argument is a number 1 through 3. Type 1 is a paired sample test; for this type of test, values in both sample1 and sample2 must be non-null or be ignored. Type 2 is for two samples with the assumption of equal variance. Type 3 is for two samples without the assumption of equal variance.

TTEST(sample1,sample2,tails,type)

FV

Calculates the future value of an investment based on these arguments:

rate (required): The interest rate per period, expressed as a ratio (e.g. 1% is 0.01).

nper (required): The total number of periods.

pmt (required): The payment made each period. Can be zero.

pv (optional): The present value; how much the investment is worth right now.

type (optional): 0/FALSE or 1/TRUE, with 0 indicating that payments are made at the end of each period, and 1 indicating they are paid at the beginning. If not specified, 0 is used.

NOTE: Unlike spreadsheet syntax, interest rates cannot be expressed as a percent. They must be expressed as a ratio (usually between 0 and 1).

See also: PV(), PMT(), RATE(), and NPER().

FV(rate,nper,pmt[,pv[,type]])

IRR

Calculates the per-period Internal Rate of Return for a set of periodic transactions. ''guess'' is a best guess of the rate of return. It is optional and the default is 0.1, meaning 10%. It is usually not necessary to specify this value.

IRR is an aggregate function.

IRR(values[,guess])

NPV

Calculates the Net Present Value for a set of periodic transactions. NPV is an aggregate function.

NPV(rate,values)

PMT

Calculates the number of periods for an investment based on constant interest rate, payments, and period duration. Arguments:

rate (required): The interest rate per period, expressed as a ratio (e.g. 1% is 0.01).

pv (required): The present value of the loan; the "principal".

fv (optional): The future value; the balance at the end of the entire annuity. If omitted, it is assumed to be 0 (e.g. a loan paid in full).

type (optional): 0/FALSE or 1/TRUE, with 0 indicating that payments are made at the end of each period, and 1 indicating they are paid at the beginning. If not specified, 0 is used.

NOTE: Unlike spreadsheet syntax, interest rates cannot be expressed as a percent. They must be expressed as a ratio (usually between 0 and 1).

See also: FV(), PV(), PMT(), and RATE().

PMT(rate,nper,pv[,fv[,type]])

PV

Calculates the present value of an investment based on these arguments:

rate (required): The interest rate per period, expressed as a ratio (e.g. 1% is 0.01).

nper (required): The total number of periods.

pmt (required): The payment made each period. Can be zero.

fv (optional): The future value; the balance at the end of the entire annuity. If omitted, it is assumed to be 0 (e.g. a loan paid in full).

type (optional): 0/FALSE or 1/TRUE, with 0 indicating that payments are made at the end of each period, and 1 indicating they are paid at the beginning. If not specified, 0 is used.

NOTE: Unlike spreadsheet syntax, interest rates cannot be expressed as a percent. They must be expressed as a ratio (usually between 0 and 1).

See also: FV(), PMT(), RATE(), and NPER().

PV(rate,nper,pmt[,fv[,type]])

RATE

Attempts to calculate the per-period interest rate of an investment. Not all arguments will have solutions; the result will be NaN if a solution cannot be found. You can also experiment with setting the guess if you believe there is a solution and it is not being returned. Note that the current implementation will not calculate negative interest rates. Arguments:

nper (required): The total number of periods.

pmt (required): The payment made each period. Can be zero.

pv (required): The present value of the loan; the "principal".

fv (optional): The future value; the balance at the end of the entire annuity. If omitted, it is assumed to be 0 (e.g. a loan paid in full).

type (optional): 0/FALSE or 1/TRUE, with 0 indicating that payments are made at the end of each period, and 1 indicating they are paid at the beginning. If not specified, 0 is used.

guess (optional): A best guess of the interest rate.

See also: FV(), PV(), PMT(), and NPER().

RATE(rate,nper,pv[,fv[,type[,guess]]])

XIRR

Calculates the Internal Rate of Return for a set of transactions that is not necessarily periodic. ''values'' is a set of transaction amounts which must contain at least one negative and one positive value. NULL or zero values in this column are ignored. ''dates'' is a list of transaction dates whose time components, if present, are ignored. ''guess'' is a best guess of the rate of return. It is optional and the default is 0.1, meaning 10%. It is usually not necessary to specify this value.

XIRR is an aggregate function.

XIRR(values,dates[,guess])

XNPV

Calculates the Net Present Value for a set of transactions that is not necessarily periodic. ''rate'' is the annualized Internal Rate of Return for the values. ''values'' is a set of transaction amounts which must contain at least one negative and one positive value. NULL or zero values in this column are ignored. ''dates'' is a list of transaction dates whose time components, if present, are ignored.

XNPV is an aggregate function.

XNPV(rate,values,dates)

ALL_TRUE

Returns TRUE if all arguments evaluate to TRUE, otherwise returns FALSE. Can be invoked with one or more arguments.

ALL_TRUE(cond1,cond2,...)

ANY_TRUE

Returns TRUE if any arguments evaluate to TRUE, otherwise returns FALSE. Can be invoked with one or more arguments.

ANY_TRUE(cond1,cond2,...)

BOOLEAN

Returns the value of expr converted to BOOLEAN type (TRUE, FALSE, or NULL).

BOOLEAN(expr), CAST(expr AS BOOLEAN)

COALESCE

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values. At least one parameter must be passed.

COALESCE(value,...)

ERROR

When evaluated, produces an error with the given error string. Depending on the transformation settings, this may halt execution entirely.

ERROR(error_string)

IF

If test is TRUE, returns exprTrue. Otherwise, returns exprFalse.

The return type is the type that best matches both exprTrue and exprFalse. For example, if exprTrue has type LONG and exprFalse has type DOUBLE, the return type will be DOUBLE, even if test is TRUE.

IF(test,exprTrue,exprFalse)

IFERROR

If the evaluation of expr1 does not produce an error, IFERROR() returns expr1; otherwise it returns expr2. Note IFERROR() will only handle errors produced at runtime. If expr1 is produces an error before even being evaluated, e.g. if it attempts to convert between inconvertible types, IFERROR() will still produce that error.

IFERROR(expr1,expr2)

IFNULL

If expr1 is not NULL, IFNULL() returns expr1; otherwise it returns expr2.

IFNULL(expr1,expr2)

NULLIF

Returns NULL if expr1 = expr2 is true, otherwise returns expr1. This is the same as CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END.

NULLIF(expr1,expr2)

CHAR_LENGTH

Returns the length of the given string argument, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH(), whereas CHAR_LENGTH() returns 5. If the argument is NULL, it returns NULL.

If the argument is not a string value, it is converted into a string.

CHAR_LENGTH(str)

CONCAT

Returns the string that results from concatenating the arguments. May have one or more arguments. All arguments are converted to strings.

CONCAT() returns NULL if any argument is NULL.

CONCAT(str1,str2,...)

DYNACAST

Returns the value of expr converted to the same type as type_expr. type_expr is not evaluated; it is only used to determine the type to which expr should be converted. In most cases, type_expr be a column reference.

DYNACAST(expr, type_expr)

GROUP_CONCAT

Returns the string that results from concatenating the arguments, separated by the separator, for the whole group. May have one or more arguments, plus the optional separator. If not set, the separator is the comma character '',''. All arguments are converted to strings.

 It is an aggregate function.

GROUP_CONCAT() returns null if there were no matching rows.

GROUP_CONCAT(str1,str2,...[SEPARATOR sep])

INITCAP

Returns str with the start of every word in uppercase and all other characters in lowercase.

INITCAP(str)

INSTR

Returns the position of the first occurrence of substring substr in string str.

If any argument is NULL, returns NULL.

INSTR(str,substr)

LCASE

LCASE() is a synonym for LOWER().

LCASE(str)

LEFT

Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

LEFT(str,len)

LENGTH

Returns the length of the string str, measured in bytes using the UTF-8 encoding. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

If str is not a string value, it is converted into a string. If str is NULL, the function returns NULL.

LENGTH(str)

LISTAGG

LISTAGG() is a simplified version of GROUP_CONCAT(). The first argument is required, and is the string to be concatenated. The second argument is optional, and is the separator string. If not set, the separator is the empty string.

LISTAGG(str1[,sep])

LOWER

Returns the string str with all characters changed to lowercase.

LOWER(str)

LPAD

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

LPAD(str, len, padstr)

LTRIM

Returns the string str with leading space characters removed.

LTRIM(str)

REGEXP_LIKE

Performs a pattern match of a string expression str against a pattern pat. The pattern can be an extended regular expression. The optional flags argument is a string that may contain any or all the following characters specifying how to perform matching:

c: Case-sensitive matching.

i: Case-insensitive matching.

m: Multiple-line mode.

n: The . character matches line terminators.

REGEXP_LIKE(str,pat[,flags])

REGEXP_REPLACE

Replaces occurrences in the string str that match the regular str specified by the pattern pat with the replacement string repl, and returns the resulting string. If str, pat, or repl is NULL, the return value is NULL. The optional flags argument behaves as it does in REGEXP_LIKE().

REGEXP_REPLACE(str,pat,repl[,flags])

REPLACE

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

REPLACE(str,from_str,to_str)

RIGHT

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

RIGHT(str,len)

SPLIT_PART

Returns the Nth substring of str split by sep. If N is zero or smaller, returns null. If there are fewer than N substrings after str has been split by the given separator, returns empty string.

Example: SPLIT_PART(''a~|~bc~cb~|~d~|~e'', ''~|~'', 2) = ''bc~cb''

SPLIT_PART(str,sep,N)

STR_CLEAN

Replaces NULL values in str with the empty string, and removes all non-whitespace control characters. These are a subset of the operations performed by STR_NORMALIZE().

STR_CLEAN(str)

STR_CONTAINS

Returns TRUE if the string str contains the string search, FALSE if it does not, or NULL if either argument is NULL.

STR_CONTAINS(str, search)

STR_DIGITS

Returns a new string consisting of only the digit characters in str in the order they appear. Converse of STR_REMOVE_DIGITS().

STR_DIGITS(str)

STR_ENDS_WITH

Returns TRUE if the string str ends with the string search, FALSE if it does not, or NULL if either argument is NULL.

STR_ENDS_WITH(str, search)

STR_NORMALIZE

Normalizes str by performing the following operations:

1. NULL values in str are replaced with the empty string.

2. Leading and trailing whitespace characters are removed.

3. All other sequences of one or more whitespace characters are replaced with a single space ('' '').

4. All non-whitespace control characters are removed.

This is the more thorough version of STR_CLEAN().

STR_NORMALIZE(str)

STR_REMOVE_DIGITS

Returns str with all digit characters removed. Converse of STR_DIGITS().

STR_REMOVE_DIGITS(str)

STR_STARTS_WITH

Returns TRUE if the string str starts with the string search, FALSE if it does not, or NULL if either argument is NULL.

STR_STARTS_WITH(str, search)

STRING

Returns the value of expr converted to a STRING. Locale, timezone, and date-format settings all affect this behavior.

STRING(expr), CAST(expr AS STRING)

SUBSTRING

The forms without a len argument return a substring from string str starting at position pos.

The forms with a len argument return a substring len characters long from string str, starting at position pos.

It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

By default, the position of the first character in the string from which the substring is to be extracted is reckoned as 1. If any argument is NULL, returns NULL.

SUBSTRING(str,pos), SUBSTRING(str FROM pos), SUBSTRING(str,pos,len), SUBSTRING(str FROM pos FOR len)

UCASE

UCASE() is a synonym for UPPER().

UCASE(str)

UPPER

Returns the string str with all characters changed to uppercase.

UPPER(str)

ADDDATE

When invoked with the INTERVAL form of the second argument, ADDDATE() is a synonym for DATE_ADD(). The related function SUBDATE() is a synonym for DATE_SUB(). For information on the INTERVAL unit argument, see the description for DATE_ADD().

When invoked with the days form of the second argument, Magic ETL treats it as an integer number of days to be added to expr.

ADDDATE(date,INTERVAL expr unit), ADDDATE(expr,days)

ADDTIME

ADDTIME() adds expr2 to expr1 and returns the result. expr1 is a time or datetime expression, and expr2 is a time expression.

ADDTIME(expr1,expr2)

CONVERT_TZ

CONVERT_TZ() converts the datetime value dt to a new moment in time such that the original value''s wall-clock time when rendered in from_tz matches the new value''s wall-clock time when rendered in to_tz.

CONVERT_TZ(dt,from_tz,to_tz)

CURDATE

Returns the current date. For long-running executions, this is the date that the execution began.

CURDATE()

CURDATETIME

CURDATETIME() is a synonym for CURRENT_TIMESTAMP().

CURDATETIME()

CURRENT_DATE

Synonym for CURDATE().

CURRENT_DATE()

CURRENT_TIME

Synonym for CURTIME().

CURRENT_TIME()

CURRENT_TIMESTAMP

Returns the current timestamp (date & time). For long-running executions, this is the timestamp of the beginning of the execution.

CURRENT_TIMESTAMP()

CURTIME

Returns the current time as a duration since the start of the current day. For long-running executions, this is the time that the execution began.

CURTIME()

DATE

Converts expr to a calendar date consisting of a year, month, and day.

DATE(expr), CAST(expr AS DATE)

DATEDIFF

Returns the number of days between two dates from datetime values.

DATEDIFF(CURRENT_DATE(), `lastmoddate`)

DATE_ADD

Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr may start with a "-" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. Permitted units are YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, and MILLISECOND.

See also DATE_SUB().

DATE_ADD(date,INTERVAL expr unit)

DATE_FORMAT

Formats the date value according to the format string.

The language used for the names is controlled by the Locale setting.

The options that can be used by DATE_FORMAT(), as well as its inverse STR_TO_DATE() and the FROM_UNIXTIME() function, are: %a: Short weekday name in current locale.

%b: Short form month name in current locale.

%c: Month with 1 or 2 digits.

%d: Day with 2 digits.

%e: Day with 1 or 2 digits.

%f: Sub seconds 6 digits.

%H: Hour with 2 digits between 00-23.

%h: Hour with 2 digits between 01-12.

%I: Hour with 2 digits between 01-12.

%i: Minute with 2 digits.

%j: Day of the year (001-366)

%k: Hour with 1 digits between 0-23.

%l: Hour with 1 digits between 1-12.

%M: Full month name in current locale.

%m: Month with 2 digits.

%r: Time in 12 hour format, followed by AM/PM. Short for ''%I:%i:%S %p''.

%S: Seconds with 2 digits.

%s: Seconds with 2 digits.

%T: Time in 24 hour format. Short for ''%H:%i:%S''.

%U: Week number (00-53), when first day of the week is Sunday.

%u: Week number (00-53), when first day of the week is Monday.

%V: Week number (01-53), when first day of the week is Sunday. Used with %X.

%v: Week number (01-53), when first day of the week is Monday. Used with %x.

%W: Full weekday name in current locale.

%w: Day of the week. 0 = Sunday, 6 = Saturday.

%X: Year with 4 digits when first day of the week is Sunday. Used with %V.

%x: Year with 4 digits when first day of the week is Monday. Used with %v.

%Y: Year with 4 digits.

%y: Year with 2 digits.

%#: For str_to_date(), skip all numbers.

%.: For str_to_date(), skip all punctation characters.

%@: For str_to_date(), skip all alpha characters.

%%: A literal % character.

DATE_FORMAT(date, format)

DATE_SUB

Performs date arithmetic. The date argument specifies the starting date or datetime value. expr is an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a "-" for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted. See DATE_ADD() for a complete list of permitted units.

DATE_SUB(date,INTERVAL expr unit)

DATE_TRUNC

DATE_TRUNC() is almost a synonym for TIMESTAMP_TRUNC(), but its argument order is flipped for compatibility reasons. See TIMESTAMP_TRUNC() for the description.

DATE_TRUNC(precision, date)

DATE_WORKING_DIFF

Returns the number of days between the dates expr1 and expr2, excluding weekends (Saturdays and Sundays). The result is negative if expr1 is after expr2.

DATE_WORKING_DIFF(expr1,expr2)

DATEDIFF

DATEDIFF() returns (expr1 – expr2) expressed as a value in days from one date to the other. expr1 and expr2 are date or date-and-time expressions. Only the date parts of the values are used in the calculation.

DATEDIFF(expr1,expr2)

DATETIME

Returns the value of expr converted to a DATETIME type, consisting of a year, month, day, hour, minute, second, and millisecond.

DATETIME(expr), CAST(expr AS DATETIME)

DAY

DAY() is a synonym for DAYOFMONTH().

DAY(date)

DAYNAME

Returns the name of the weekday for date. The language used for the name is controlled by the Locale setting.

DAYNAME(date)

DAYOFMONTH

Returns the day of the month for date, in the range 1 to 31.

DAYOFMONTH(date)

DAYOFWEEK

Returns the day of the week index for the date (1 = Sunday, 2 = Monday, ..., 7 = Saturday). These index values correspond to the ODBC standard.

This contrasts with WEEKDAY() which follows a different index numbering (0 = Monday, 1 = Tuesday, ... 6 = Sunday).

DAYOFWEEK(date)

DAYOFYEAR

Returns the day of the year for date, in the range 1 to 366.

DAYOFYEAR(date)

DURATION

Returns the value of expr converted to a DURATION, consisting of hours, minutes, seconds, and milliseconds.

DURATION(expr), CAST(expr AS DURATION)

FROM_DAYS

Given a day number N, returns a DATE value. The day count is based on the number of days from the start of the standard calendar.

The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn''t account for the lost days when the calendar changed from the Julian calendar.

This is the converse of the TO_DAYS() function.

FROM_DAYS(N)

FROM_UNIXTIME

Given a day number N, returns a DATE value. The day count is based on the number of days from the start of the standard calendar.

The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn''t account for the lost days when the calendar changed from the Julian calendar.

This is the converse of the TO_DAYS() function.

FROM_DAYS(N)

HOUR

Returns the hour for time. The range of the return value is 0 to 23.

HOUR(time)

LAST_DAY

Takes a date or datetime value and returns the corresponding value for the last day of the month.

LAST_DAY(date)

MICROSECOND

Returns the microseconds from the datetime expression expr as a number in the range from 0 to 999999.

MICROSECOND(expr)

MILLISECOND

Returns the milliseconds from the datetime expression expr as a number in the range from 0 to 999.

MILLISECOND(expr)

MINUTE

Returns the minute of the datetime argument, in the range 0 to 59.

MINUTE(time)

MONTH

Returns the month for date in the range 1 to 12 for January to December.

MONTH(date)

MONTHNAME

Returns the full name of the month for date. The language used for the name is controlled by the Locale setting.

MONTHNAME(date)

NOW

Synonym for CURRENT_TIMESTAMP().

NOW()

PERIOD_ADD

Adds N months to period P. P is an integer in the format YYMM or YYYYMM. If P contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.

Returns an integer value in the format YYYYMM.

PERIOD_ADD(P,N)

PERIOD_DIFF

Returns the number of months between periods P1 and P2. P1 and P2 can be in the format YYMM or YYYYMM, and are integer values.

If P1 or P2 contains a two-digit year, values from 00 to 69 are converted to from 2000 to 2069, while values from 70 are converted to 1970 upwards.

PERIOD_DIFF(P1,P2)

QUARTER

Returns the quarter of the year for date, in the range 1 to 4. Returns NULL if the given value is NULL.

QUARTER(date)

SEC_TO_TIME

Returns the seconds argument, converted to hours, minutes, and seconds, as a TIME value.

SEC_TO_TIME(seconds)

SECOND

Returns the second for a given time in the range 0 to 59.

SECOND(time)

STR_TO_DATE

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value.

The format that can be used by STR_TO_DATE() is described in the description of DATE_FORMAT().

STR_TO_DATE(str,format)

SUBDATE

When invoked with the INTERVAL form of the second argument, SUBDATE() is a synonym for DATE_SUB(). See DATE_ADD() for a complete list of permitted units.

The second form allows the use of an integer value for days. In such cases, it is interpreted as the number of days to be subtracted from the date or datetime expression expr.

SUBDATE(date,INTERVAL expr unit), SUBDATE(expr,days)

SUBTIME

SUBTIME() returns expr1 - expr2 expressed as a value of the same type as expr1. expr1 is a time or datetime expression, and expr2 is a time expression.

SUBTIME(expr1,expr2)

SYSDATE

Synonym for CURRENT_TIMESTAMP().

SYSDATE()

TIME

Converts expr to a duration (TIME-type) consisting of hours, minutes, seconds, and milliseconds.

TIME(expr)

TIME_FORMAT

This is used like the DATE_FORMAT() function, but the format string may contain format specifiers only for hours, minutes, and seconds. Other specifiers produce a NULL value or 0.

TIME_FORMAT(time,format)

TIME_TO_SEC

Returns the time argument, converted to seconds.

TIME_TO_SEC(time)

TIMEDIFF

TIMEDIFF() returns expr1 - expr2 expressed as a time value. expr1 and expr2 are time or date-and-time expressions, but both must be of the same type.

TIMEDIFF(expr1,expr2)

TIMESTAMP

Converts expr to a timestamp (DATETIME-type) consisting of year, month, day, hour, minute, second, and millisecond.

TIMESTAMP(expr)

TIMESTAMP_TRUNC

Truncates timestamp to the given precision, or to the day if no precision is specified. The result is of type DATE if timestamp is a DATE, otherwise it has type DATETIME. The value of precision is a constant string matching one of the following:

Millennium: MILLENNIUM

Century: CENTURY

Year: YEAR

ISO Year: ISOYEAR

Quarter: QUARTER

Month: MONTH

Same weekday as first day of year: YEARWEEK

Same weekday as first day of ISO year: ISOWEEK

Same weekday as first day of month: MONTHWEEK

First day of week according to current Locale setting: WEEK

Day (the default): DAY

Hour: HOUR

Minute: MINUTE

Second: SECOND

Millisecond: MILLISECOND

Microsecond: MICROSECOND

TIMESTAMP_TRUNC(timestamp[,precision])

TO_DAYS

Given a date date, returns the number of days since the start of the current calendar.

The function is not designed for use with dates before the advent of the Gregorian calendar in October 1582. Results will not be reliable since it doesn''t account for the lost days when the calendar changed from the Julian calendar.

This is the converse of the FROM_DAYS() function.

TO_DAYS(date)

TODAY

TODAY() is a synonym for CURDATE().

TODAY()

TRIM

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str), TRIM([remstr FROM] str)

TRUNC

TRUNC() is a synonym for TIMESTAMP_TRUNC(). It is not to be confused with TRUNCATE(), which operates on numeric values.

TRUNC(timestamp[,precision])

UNIX_TIMESTAMP

If called with no argument, returns a Unix timestamp (seconds since ''1970-01-01 00:00:00'' UTC) as an unsigned integer. If UNIX_TIMESTAMP() is called with a date argument, it returns the value of the argument as seconds since ''1970-01-01 00:00:00'' UTC. date may be a DATE, a DATETIME, or a date string. The server interprets date as a value in the current time zone and converts it to an internal value in UTC. Clients can set their time zone in Magic action settings, DataFlow settings, or customer settings.

The inverse function of UNIX_TIMESTAMP() is FROM_UNIXTIME().

UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

WEEK

This function returns the week number for date. The two-argument form of WEEK() allows you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53. If the mode argument is omitted, the value of the default_week_format system variable is used. Modes are as follows:

0: First day is Sunday, range is 0-53, first week has a Sunday in this year.

1: First day is Monday, range is 0-53, first week has more than 3 days this year.

2: First day is Sunday, range is 1-53, first week has a Sunday in this year.

3: First day is Monday, range is 1-53, first week has more than 3 days this year.

4: First day is Sunday, range is 0-53, first week has more than 3 days this year.

5: First day is Monday, range is 0-53, first week has a Monday in this year.

6: First day is Sunday, range is 1-53, first week has more than 3 days this year.

7: First day is Monday, range is 1-53, first week has a Monday in this year.

WEEK(date[,mode])

WEEKDAY

Returns the weekday index for date (0 = Monday, 1 = Tuesday, ... 6 = Sunday).

This contrasts with DAYOFWEEK() which follows the ODBC standard (1 = Sunday, 2 = Monday, ..., 7 = Saturday).

WEEKDAY(date)

WEEKOFYEAR

WEEKOFYEAR() is a synonym for WEEK().

WEEKOFYEAR(date[,mode])

YEAR

Returns the year for the given date, in the range 0 to 9999.

YEAR(date)

YEARWEEK

Returns year and week for a date. The mode argument works exactly like the mode argument to WEEK(). The year in the result may be different from the year in the date argument for the first and the last week of the year.

YEARWEEK(date), YEARWEEK(date,mode)

LOCALE

For debugging purposes. Returns the Locale setting for this context. This setting controls the behavior of functions like DAYNAME().

@@LOCALE

SYSTEM_LOCALE

For debugging purposes. Returns the Locale setting for the DataFlow or, if not set, the customer. This setting controls the behavior of functions like DAYNAME(). This setting might be overridden by an action- or statement-level control (see @@LOCALE).

@@SYSTEM_LOCALE

SYSTEM_TZ

For debugging purposes. Returns the time zone setting for the DataFlow or, if not set, the customer. This setting controls the behavior of many date- and time-related functions, like STR_TO_DATE(). This setting might be overridden by an action- or statement-level control (see @@TZ).

@@SYSTEM_TZ

TZ

For debugging purposes. Returns the time zone setting for this context. This setting controls the behavior of many date- and time-related functions, like STR_TO_DATE().

@@TZ

DX_ACTION_ID

The system ID of the action executing the statement. This is different from @@DX_ACTION_NAME.

@@DX_ACTION_ID

DX_ACTION_NAME

The user-provided name of the action executing the statement. This is different from @@DX_ACTION_ID.

@@DX_ACTION_NAME

DX_DATAFLOW_ID

The numeric ID of the DataFlow executing the statement, or NULL if the statement is not being executed by a saved DataFlow. This is different from @@DX_DATAFLOW_NAME.

@@DX_DATAFLOW_ID

DX_DATAFLOW_NAME

The user-provided name of the DataFlow executing the statement, or NULL if the statement is not being executed by a DataFlow. This is different from @@DX_DATAFLOW_ID.

@@DX_DATAFLOW_NAME

DX_ENGINE

The execution engine, e.g. MAGIC.

@@DX_ENGINE

DX_EXECUTION_ID

The numeric ID of the current DataFlow execution, or null if the statement is not being executed as part of a DataFlow execution. Note that these IDs are not sequential for a given DataFlow, but are guaranteed to be larger than the IDs of executions started earlier.

@@DX_EXECUTION_ID

DX_IS_PREVIEW

TRUE if the statement is being executed as part of a DataFlow preview, otherwise FALSE.

@@DX_IS_PREVIEW