Intro
This reference lists the available SQL expressions in Magic ETL. For a description and more information about a specific expression, see the Magic ETL UI. Learn how to use these expressions in the SQL tile while creating a Magic ETL DataFlow.| Expression Name | Syntax | Category | Available for Magic on Cloud Compute? |
|---|---|---|---|
| ! | ! cond | LOGIC | Y |
| != | X!= Y | LOGIC | Y |
| !~ | X!~ Y | STRING | - |
| !~* | X!~* Y | STRING | - |
| () | (expr) | NUMERIC | Y |
| * | X * Y | NUMERIC | Y |
| ’+ | X + Y | NUMERIC | Y |
| - | X - Y | NUMERIC | Y |
| / | X / Y | NUMERIC | Y |
| :: | expr::TYPE | LOGIC | Y |
| < | X < Y | LOGIC | Y |
| <= | X <= Y | LOGIC | Y |
| <=> | X <=> Y | LOGIC | - |
| <> | X <> Y | LOGIC | Y |
| ’= | X = Y | LOGIC | Y |
| > | X > Y | LOGIC | Y |
| >= | X >= Y | LOGIC | Y |
| ABS | ABS(X) | NUMERIC | Y |
| ACOS | ACOS(X) | NUMERIC | - |
| ADDDATE | ADDDATE(date,INTERVAL expr unit) | TIME | Y |
| ADDTIME | ADDTIME(expr1,expr2) | TIME | Y |
| AND | cond1 AND cond2 | LOGIC | Y |
| ANOVAF | ANOVAF(sample1,sample2[,sample3[,…,sampleN]]) | STATISTICAL | - |
| ANOVAP | ANOVAP(sample1,sample2[,sample3[,…,sampleN]]) | STATISTICAL | - |
| APPROXIMATE_COUNT_DISTINCT | APPROXIMATE_COUNT_DISTINCT(X) | NUMERIC | Y |
| ASIN | ASIN(X) | NUMERIC | - |
| ATAN | ATAN(X) | NUMERIC | - |
| ATAN2 | ATAN2(Y,X) | NUMERIC | - |
| AVG | AVG(expr) | NUMERIC | Y |
| BETWEEN | expr1 BETWEEN expr2 AND expr3 | LOGIC | Y |
| BIT_AND | BIT_AND(X, Y) | NUMERIC | - |
| BIT_LSHIFT | BIT_LSHIFT(X, Y) | NUMERIC | - |
| BIT_NAND | BIT_NAND(X, Y) | NUMERIC | - |
| BIT_NOR | BIT_NOR(X, Y) | NUMERIC | - |
| BIT_NOT | BIT_NOT(X) | NUMERIC | - |
| BIT_OR | BIT_OR(X, Y) | NUMERIC | - |
| BIT_RSHIFT | BIT_RSHIFT(X, Y) | NUMERIC | - |
| BIT_XNOR | BIT_XNOR(X, Y) | NUMERIC | - |
| BIT_XOR | BIT_XOR(X, Y) | NUMERIC | - |
| BOOLEAN | BOOLEAN(expr) | LOGIC | - |
| CASE | CASE WHEN cond1 THEN expr1 WHEN condN THEN exprN ELSE elseExpr END | LOGIC | Y |
| CAST | CAST(expr AS type) | LOGIC | Y |
| CBRT | CBRT(X) | NUMERIC | - |
| CEIL | CEIL(X) | NUMERIC | Y |
| CEILING | CEILING(X) | NUMERIC | Y |
| CHAR_LENGTH | CHAR_LENGTH(str) | STRING | Y |
| COALESCE | COALESCE(value,…) | LOGIC | Y |
| CONCAT | CONCAT(str1,str2,…) | STRING | Y |
| CONV | CONV(N,from_base,to_base) | NUMERIC | - |
| CONVERT_TZ | CONVERT_TZ(dt,from_tz,to_tz) | TIME | Y |
| CONVERT_UNIT | CONVERT_UNIT(expr,from_unit,to_unit) | NUMERIC | - |
| CORR | CORR(x,y) | STATISTICAL | - |
| COS | COS(X) | NUMERIC | - |
| COSH | COSH(X) | NUMERIC | - |
| COT | COT(X) | NUMERIC | - |
| COUNT | COUNT(expr) | NUMERIC | Y |
| COUNT_DISTINCT | COUNT_DISTINCT(expr) | NUMERIC | Y |
| COVAR_POP | COVAR_POP(x,y) | STATISTICAL | - |
| COVAR_SAMP | COVAR_SAMP(x,y) | STATISTICAL | - |
| CRC32 | CRC32(expr) | NUMERIC | - |
| CURDATE | CURDATE() | TIME | Y |
| CURDATETIME | CURDATETIME() | TIME | Y |
| CURRENT_DATE | CURRENT_DATE() | TIME | Y |
| CURRENT_TIME | CURRENT_TIME() | TIME | Y |
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP() | TIME | Y |
| CURTIME | CURTIME() | TIME | Y |
| DATE | DATE(expr) | TIME | Y |
| DATEDIFF | DATEDIFF([unit,]expr1,expr2) | TIME | Y |
| DATETIME | DATETIME(expr) | TIME | Y |
| DATE_ADD | DATE_ADD(date,INTERVAL expr unit) | TIME | Y |
| DATE_FORMAT | DATE_FORMAT(date, format) | TIME | Y |
| DATE_SUB | DATE_SUB(date,INTERVAL expr unit) | TIME | Y |
| DATE_TRUNC | DATE_TRUNC(precision, date) | TIME | Y |
| DATE_WORKING_DIFF | DATE_WORKING_DIFF(expr1,expr2) | TIME | - |
| DAY | DAY(date) | TIME | Y |
| DAYNAME | DAYNAME(date) | TIME | Y |
| DAYOFMONTH | DAYOFMONTH(date) | TIME | Y |
| DAYOFWEEK | DAYOFWEEK(date) | TIME | Y |
| DAYOFYEAR | DAYOFYEAR(date) | TIME | Y |
| DECIMAL | DECIMAL(expr) | NUMERIC | Y |
| DEGREES | DEGREES(X) | NUMERIC | - |
| DISTANCE | DISTANCE(lat0, lon0, lat1, lon1) | NUMERIC | - |
| DISTANCEV | DISTANCEV(lat0, lon0, lat1, lon1) | NUMERIC | - |
| DIV | DIV(X,Y) | NUMERIC | Y |
| DOUBLE | DOUBLE(expr) | NUMERIC | Y |
| DURATION | DURATION(expr) | TIME | - |
| DX_ACTION_ID | ‘@@DX_ACTION_ID | SYSTEM | - |
| DX_ACTION_NAME | ‘@@DX_ACTION_NAME | SYSTEM | - |
| DX_DATAFLOW_ID | ‘@@DX_DATAFLOW_ID | SYSTEM | - |
| DX_DATAFLOW_NAME | ‘@@DX_DATAFLOW_NAME | SYSTEM | - |
| DX_ENGINE | ‘@@DX_ENGINE | SYSTEM | - |
| DX_EXECUTION_ID | ‘@@DX_EXECUTION_ID | SYSTEM | - |
| DX_IS_PREVIEW | ‘@@DX_IS_PREVIEW | SYSTEM | - |
| DYNACAST | DYNACAST(expr, type_expr) | STRING | Y |
| E | E() | NUMERIC | - |
| EPOCH_MILLIS | EPOCH_MILLIS([timestamp]) | TIME | - |
| ERROR | ERROR(error_string) | LOGIC | - |
| EXP | EXP(X) | NUMERIC | - |
| EXPRESS | EXPRESS(expr,substitution1,substitution2,…) | LOGIC | - |
| EXPRESSION | EXPRESS(expr) | LOGIC | - |
| EXPR_COLUMN | EXPR_COLUMN(str) | LOGIC | - |
| EXPR_FORMAT | EXPR_FORMAT | LOGIC | - |
| EXPR_PARSE | EXPR_PARSE(str) | LOGIC | - |
| EXPR_PUSH | EXPR_PUSH(expr,substitution) | LOGIC | - |
| EXPR_VALUE | EXPR_VALUE(expr) | LOGIC | - |
| EXTRACT | EXTRACT(field FROM expr) | TIME | Y |
| FINITE | FINITE(expr) | NUMERIC | - |
| FIRST_NON_NULL_VALUE | FIRST_NON_NULL_VALUE(expr) | LOGIC | Y |
| FIRST_VALUE | FIRST_VALUE(expr) | LOGIC | Y |
| FLOOR | FLOOR(X) | NUMERIC | Y |
| FROM_BASE64 | FROM_BASE64(str) | STRING | - |
| FROM_DAYS | FROM_DAYS(N) | TIME | Y |
| FROM_EPOCH_MILLIS | FROM_EPOCH_MILLIS(epoch_millis) | TIME | - |
| FROM_UNIXTIME | FROM_UNIXTIME(unix_timestamp) | TIME | Y |
| FUZZY_PARSE_DATE | FUZZY_PARSE_DATE(str) | TIME | - |
| FV | FV(rate,nper,pmt[,pv[,type]]) | FINANCIAL | - |
| GREATEST | GREATEST(value,…) | LOGIC | - |
| GROUP_CONCAT | GROUP_CONCAT([DISTINCT] str1,str2,…[SEPARATOR sep]) | STRING | Y |
| HOUR | HOUR(time) | TIME | Y |
| IFERROR | IFERROR(expr1,expr2) | LOGIC | - |
| IFNULL | IFNULL(expr1,expr2) | LOGIC | Y |
| IN | expr1 IN (expr2,expr3,…) | LOGIC | Y |
| INFINITY | INFINITY() | NUMERIC | - |
| INITCAP | INITCAP(str) | STRING | Y |
| INSTR | INSTR(str,substr) | STRING | Y |
| IRR | IRR(values[,guess]) | FINANCIAL | - |
| IS NULL | expr IS NULL | LOGIC | Y |
| LAST_DAY | LAST_DAY(date) | TIME | Y |
| LAST_NON_NULL_VALUE | LAST_NON_NULL_VALUE(expr) | LOGIC | Y |
| LAST_VALUE | LAST_VALUE(expr) | LOGIC | Y |
| LCASE | LCASE(str) | STRING | Y |
| LEAST | LEAST(value,…) | LOGIC | - |
| LEFT | LEFT(str,len) | STRING | Y |
| LENGTH | LENGTH(str) | STRING | Y |
| LISTAGG | LISTAGG(str1[,sep]) | STRING | Y |
| LN | LN(X) | NUMERIC | - |
| LOCALE | ‘@@LOCALE | TIME | - |
| LOG | LOG([B,]X) | NUMERIC | - |
| LOG10 | LOG10(X) | NUMERIC | - |
| LOG2 | LOG2(X) | NUMERIC | - |
| LONG | LONG(expr) | NUMERIC | Y |
| LONG_MAX | LONG_MAX() | NUMERIC | - |
| LONG_MIN | LONG_MIN() | NUMERIC | - |
| LOWER | LOWER(str) | STRING | Y |
| LPAD | LPAD(str, len, padstr) | STRING | Y |
| LTRIM | LTRIM(str) | STRING | Y |
| MAX | MAX(expr) | NUMERIC | Y |
| MD5 | MD5(expr1,expr2…,exprN) | NUMERIC | - |
| MEDIAN | MEDIAN(expr) | STATISTICAL | Y |
| MICROSECOND | MICROSECOND(expr) | TIME | Y |
| MILLISECOND | MILLISECOND(expr) | TIME | Y |
| MIN | MIN(expr) | NUMERIC | Y |
| MINUTE | MINUTE(time) | TIME | Y |
| MOD | MOD(N,M) | NUMERIC | Y |
| MONTH | MONTH(date) | TIME | Y |
| MONTHNAME | MONTHNAME(date) | TIME | Y |
| NAN | NAN() | NUMERIC | - |
| NEGATIVE_INFINITY | NEGATIVE_INFINITY() | NUMERIC | - |
| NOT | NOT cond | LOGIC | Y |
| NOW | NOW() | TIME | Y |
| NPER | NPER(rate,pmt,pv[,fv[,type]]) | FINANCIAL | - |
| NPV | NPV(rate,values) | FINANCIAL | - |
| NULL | NULL | LOGIC | Y |
| NULLIF | NULLIF(expr1,expr2) | LOGIC | Y |
| OCTET_LENGTH | OCTET_LENGTH(str) | STRING | Y |
| OR | cond1 OR cond2 | LOGIC | Y |
| PERCENTILE | PERCENTILE(expr, ntile) | STATISTICAL | Y |
| PERIOD | PERIOD(expr) | TIME | - |
| PERIOD_ADD | PERIOD_ADD(P,N) | TIME | Y |
| PERIOD_DIFF | PERIOD_DIFF(P1,P2) | TIME | Y |
| PI | PI() | NUMERIC | - |
| PMT | PMT(rate,nper,pv[,fv[,type]]) | FINANCIAL | - |
| POW | POW(X,Y) | NUMERIC | Y |
| POWER | POWER(X,Y) | NUMERIC | Y |
| PV | PV(rate,nper,pmt[,fv[,type]]) | FINANCIAL | - |
| QUARTER | QUARTER(date) | TIME | Y |
| RADIANS | RADIANS(X) | NUMERIC | - |
| RAND | RAND([N]) | NUMERIC | Y |
| RATE | RATE(rate,nper,pv[,fv[,type[,guess]]]) | FINANCIAL | - |
| REGEXP | X REGEXP Y | STRING | - |
| REGEXP_LIKE | REGEXP_LIKE(str,pat[,flags]) | STRING | - |
| REGEXP_REPLACE | REGEXP_REPLACE(str,pat,repl[,flags]) | STRING | Y |
| REPLACE | REPLACE(str,from_str,to_str) | STRING | Y |
| REVERSE | REVERSE(str) | STRING | - |
| RIGHT | RIGHT(str,len) | STRING | Y |
| RLIKE | X RLIKE Y | STRING | - |
| ROUND | ROUND(X[,D]) | NUMERIC | Y |
| ROW_NUMBER | ROW_NUMBER() | LOGIC | Y |
| RPAD | RPAD(str, len, padstr) | NUMERIC | Y |
| RTRIM | RTRIM(str) | NUMERIC | Y |
| SECOND | SECOND(time) | TIME | Y |
| SEC_TO_TIME | SEC_TO_TIME(seconds) | TIME | Y |
| SHA1 | SHA1(expr1,expr2…,exprN) | NUMERIC | - |
| SIGN | SIGN(X) | NUMERIC | - |
| SIN | SIN(X) | NUMERIC | - |
| SINH | SINH(X) | NUMERIC | - |
| SPLIT_PART | SPLIT_PART(str,sep,N) | STRING | Y |
| SQRT | SQRT(X) | NUMERIC | Y |
| SQUASH_WHITESPACE | SQUASH_WHITESPACE(str) | STRING | Y |
| STD | STD(expr) | STATISTICAL | Y |
| STDDEV | STDDEV(expr) | STATISTICAL | Y |
| STDDEV_POP | STDDEV_POP(expr) | STATISTICAL | Y |
| STDDEV_SAMP | STDDEV_SAMP(expr) | STATISTICAL | Y |
| STRING | STRING(expr) | STRING | Y |
| STR_CONTAINS | STR_CONTAINS(str, search) | STRING | Y |
| STR_DIGITS | STR_DIGITS(str) | STRING | Y |
| STR_ENDS_WITH | STR_ENDS_WITH(str, search) | STRING | Y |
| STR_NORMALIZE | STR_NORMALIZE(str) | STRING | - |
| STR_REMOVE_DIGITS | STR_REMOVE_DIGITS(str) | STRING | Y |
| STR_STARTS_WITH | STR_STARTS_WITH(str, search) | STRING | Y |
| STR_TO_CHARNAMES | STR_TO_CHARNAMES(str) | STRING | - |
| STR_TO_DATE | STR_TO_DATE(str,format) | TIME | Y |
| STR_TO_HEX | STR_TO_HEX(str) | STRING | - |
| SUBDATE | SUBDATE(date,INTERVAL expr unit) | TIME | Y |
| SUBSTRING | SUBSTRING(str,pos[,len]) | STRING | Y |
| SUBSTRING_INDEX | SUBSTRING_INDEX(src,delimiter,count) | STRING | - |
| SUBTIME | SUBTIME(expr1,expr2) | TIME | Y |
| SUM | SUM(expr) | NUMERIC | Y |
| SYSDATE | SYSDATE() | TIME | Y |
| SYSTEM_LOCALE | ‘@@SYSTEM_LOCALE | TIME | - |
| SYSTEM_TZ | ‘@@SYSTEM_TZ | TIME | - |
| TAN | TAN(X) | NUMERIC | - |
| TANH | TANH(X) | NUMERIC | - |
| TIME | TIME(expr) | TIME | Y |
| TIMEDIFF | TIMEDIFF(expr1,expr2) | TIME | Y |
| TIMESTAMP | TIMESTAMP(expr) | TIME | Y |
| TIMESTAMP_TRUNC | TIMESTAMP_TRUNC(timestamp[,precision]) | TIME | Y |
| TIME_FORMAT | TIME_FORMAT(time,format) | TIME | Y |
| TIME_TO_SEC | TIME_TO_SEC(time) | TIME | Y |
| TODAY | TODAY() | TIME | Y |
| TO_BASE64 | TO_BASE64(str) | STRING | - |
| TO_DAYS | TO_DAYS(date) | TIME | Y |
| TRIM | TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) | TIME | Y |
| TRUNC | TRUNC(timestamp[,precision]) | TIME | Y |
| TRUNCATE | TRUNCATE(X,D) | NUMERIC | Y |
| TRY_CAST | TRY_CAST(expr AS type) | LOGIC | - |
| TTEST | TTEST(sample1,sample2,tails,type) | STATISTICAL | - |
| TZ | ‘@@TZ | TIME | - |
| UCASE | UCASE(str) | STRING | Y |
| UNIX_TIMESTAMP | UNIX_TIMESTAMP([date]) | TIME | Y |
| UPPER | UPPER(str) | STRING | Y |
| VARIANCE | VARIANCE(expr) | STATISTICAL | Y |
| VARIANCE_POP | VARIANCE_POP(expr) | STATISTICAL | Y |
| VARIANCE_SAMP | VARIANCE_SAMP(expr) | STATISTICAL | Y |
| VAR_POP | VAR_POP(expr) | STATISTICAL | Y |
| VAR_SAMP | VAR_SAMP(expr) | STATISTICAL | Y |
| WEEK | WEEK(date[,mode]) | TIME | Y |
| WEEKDAY | WEEKDAY(date) | TIME | Y |
| WEEKOFYEAR | WEEKOFYEAR(date[,mode]) | TIME | Y |
| WIDTH_BUCKET | WIDTH_BUCKET(expr, min, max, bucket_count) | NUMERIC | Y |
| XIRR | XIRR(values,dates[,guess]) | FINANCIAL | - |
| XNPV | XNPV(rate,values,dates) | FINANCIAL | - |
| XOR | cond1 XOR cond2 | LOGIC | - |
| YEAR | YEAR(date) | TIME | Y |
| YEARWEEK | YEARWEEK(date[,mode]) | TIME | Y |
| ~ | X ~ Y | STRING | - |
| ~* | X ~* Y | STRING | - |