Skip to main content

Version 6

# Intro

You can use this reference guide to learn about supported functions in the Add Formula and Group By tiles in Magic ETL v2.

Important: There are significant behavioral differences in Magic ETL v2. Read the Upgrading to Magic ETL v2 (Beta) article before converting mission-critical Magic ETL DataFlows. Failure to do so may risk an unintended change to your DataFlow’s behavior.

# 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: Important: While the Formula Editor looks similar to the Beast Mode editor, they are not the same. You can copy any Beast Mode you have made into the Formula Editor and it will work, but not all of the functions allowed in the Formula Editor will work inside of a Beast Mode. This is due to the increased processing power available to DataFlows versus the Analyzer.

# 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 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)

# Numeric Functions

 Function Name Description Example 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)

# Statistical Functions

 Function Name Description Example 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)

# 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 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)

# 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 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)

# String Functions

 Function Description Example 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)

# 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 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

# 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 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