Intro
You can use this reference guide to learn about supported functions in the Add Formula and Group By tiles in Magic ETL.
Supported Functions
The following sections give examples of the available functions that can be used inside of Magic ETL. However, for a complete list you will need to refer to the Functions list inside of the Formula Editor as seen here:
Aggregate Functions
Function Name 
Description 
Example 
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 nonnull pairs of values only. 
CORR(x,y) 
COUNT 
Returns a count of the number of nonNULL 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 nonnull pairs of values only. 
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 nonnull pairs of values only. 
COVAR_SAMP(x,y) 
FIRST_NON_NULL_VALUE 
Returns the first nonnull value in expr, or NULL if no nonNULL 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 nonnull value in expr, or NULL if no nonNULL 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) 
Numeric Functions
Function Name 
Description 
Example 
ABS 
Returns the absolute (nonnegative) 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 casesensitive. 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), ''halfgal'' (half gallons), ''kilogrammeters/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 32bit 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 fixedprecision 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 WGS84 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 floatingpoint DOUBLE type. 
DOUBLE(expr), CAST(expr AS DOUBLE) 
E 
Returns the value of e (Euler's number) as a doubleprecision 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 basee 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 base10 logarithm of X. 
LOG10(X) 
LOG2 
Returns the base2 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 (notanumber). 
NAN() 
NEGATIVE_INFINITY 
Returns the floating point representation of negative infinity. 
NEGINF(), NEGATIVE_INFINITY() 
PI 
Returns the value of π (pi) as a doubleprecision 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, rightpadded 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 equalwidth 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) 
Statistical Functions
Function Name 
Description 
Example 
ANOVAF 
Returns the Fvalue of a oneway Analysis of Variance of the samples. At least two samples must be provided, and each sample must have at least two nonnull values. Note that a oneway ANOVA on two samples is equivalent to the Student's ttest (see the TTEST function). See also: ANOVAP() 
ANOVAF(sample1,sample2[,sample3[,...,sampleN]]) 
ANOVAP 
Returns the pvalue of a oneway Analysis of Variance of the samples. At least two samples must be provided, and each sample must have at least two nonnull values. Note that a oneway ANOVA on two samples is equivalent to the Student's ttest (see the TTEST function). See also: ANOVAF() 
ANOVAP(sample1,sample2[,sample3[,...,sampleN]]) 
TTEST 
Returns the pvalue result of a Student's ttest. Arguments sample1 and sample2 are two numeric columns with at least 2 nonnull values each. The tails argument is either 1 or 2, indicating a one or twotailed 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 nonnull 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) 
Financial Functions
Function Name 
Description 
Example 
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 perperiod 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 perperiod 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) 
Logical Functions
Function Name 
Description 
Example 
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 nonNULL value in the list, or NULL if there are no nonNULL 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) 
String Functions
Function 
Description 
Example 
CHAR_LENGTH 
Returns the length of the given string argument, measured in characters. A multibyte character counts as a single character. This means that for a string containing five twobyte 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.
Note: The GROUP_CONCAT function does not support “ORDER BY” or "DISTINCT" as part of the expression in Magic ETL v2.

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 UTF8 encoding. A multibyte character counts as multiple bytes. This means that for a string containing five twobyte 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, leftpadded 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: Casesensitive matching. i: Caseinsensitive matching. m: Multipleline 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 casesensitive 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~cbde', '~', 2) = "bc~cb" 
SPLIT_PART(str,sep,N) 
STR_CLEAN 
Replaces NULL values in str with the empty string, and removes all nonwhitespace 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 nonwhitespace 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 dateformat 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) 
Date and Time Functions
Function 
Description 
Example 
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 wallclock time when rendered in from_tz matches the new value''s wallclock time when rendered in to_tz. 
CONVERT_TZ(dt,from_tz,to_tz) 
CURDATE 
Returns the current date. For longrunning 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 longrunning 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 longrunning 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 0023. %h: Hour with 2 digits between 0112. %I: Hour with 2 digits between 0112. %i: Minute with 2 digits. %j: Day of the year (001366) %k: Hour with 1 digits between 023. %l: Hour with 1 digits between 112. %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 (0053), when first day of the week is Sunday. %u: Week number (0053), when first day of the week is Monday. %V: Week number (0153), when first day of the week is Sunday. Used with %X. %v: Week number (0153), 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 dateandtime 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 twodigit 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 twodigit 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 (TIMEtype) 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 dateandtime expressions, but both must be of the same type. 
TIMEDIFF(expr1,expr2) 
TIMESTAMP 
Converts expr to a timestamp (DATETIMEtype) 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 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 ''19700101 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 ''19700101 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 twoargument 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 053, first week has a Sunday in this year. 1: First day is Monday, range is 053, first week has more than 3 days this year. 2: First day is Sunday, range is 153, first week has a Sunday in this year. 3: First day is Monday, range is 153, first week has more than 3 days this year. 4: First day is Sunday, range is 053, first week has more than 3 days this year. 5: First day is Monday, range is 053, first week has a Monday in this year. 6: First day is Sunday, range is 153, first week has more than 3 days this year. 7: First day is Monday, range is 153, 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 statementlevel 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 timerelated functions, like STR_TO_DATE(). This setting might be overridden by an action or statementlevel 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 timerelated functions, like STR_TO_DATE(). 
@@TZ 
System Functions
Function Name 
Description 
Example 
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 userprovided 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 userprovided 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 
Comments
0 comments
Please sign in to leave a comment.