Navigation
This version of the documentation is archived and no longer supported.
Supported SQL Functions and Operators
MongoDB Connector for BI Version 2.0.0 is compatible with SQL-99 SELECT statements.
The following tables list the various MySQL functions and operators
supported in MongoDB Connector for BI 2.0.0.
Comparison Functions and Operators
Function/Operator |
Description |
BETWEEN ... AND ... |
Check whether a value is within a range of values |
COALESCE() |
Return the first non-NULL argument |
= |
Equal operator |
<=> |
NULL-safe equal to operator |
> |
Greater than operator |
>= |
Greater than or equal operator |
GREATEST() |
Return the largest argument |
IN() |
Check whether a value is within a set of values |
IS |
Test a value against a boolean |
IS NOT |
Test a value against a boolean |
IS NOT NULL |
NOT NULL value test |
IS NULL |
NULL value test |
ISNULL() |
Test whether the argument is NULL |
LEAST() |
Return the smallest argument |
< |
Less than operator |
<= |
Less than or equal operator |
LIKE |
Simple pattern matching |
NOT BETWEEN ... AND ... |
Check whether a value is not within a range of values |
!= , <> |
Not equal operator |
NOT IN() |
Check whether a value is not within a set of values |
Logical Operators
Function/Operator |
Description |
AND , && |
Logical AND |
NOT , ! |
Negates value |
|| , OR |
Logical OR |
XOR |
Logical XOR |
Control Flow Functions and Operators
Function/Operator |
Description |
CASE |
Case operator |
IF() |
If/else construct |
IFNULL() |
Null if/else construct |
NULLIF() |
Return NULL if expr1 = expr2 |
String Functions
Function/Operator |
Description |
ASCII() |
Return numeric value of left-most character |
CHAR_LENGTH() |
Return number of characters in argument |
CHARACTER_LENGTH() |
Synonym for CHAR_LENGTH() |
CONCAT() |
Return concatenated string |
CONCAT_WS() |
Return concatenate with separator |
ELT() |
Return string at index number |
INSERT() |
Insert a substring at the specified position up to the specified
number of characters |
INSTR() |
Return the index of the first occurrence of substring |
LCASE() |
Synonym for LOWER() |
LEFT() |
Return the leftmost number of characters as specified |
LENGTH() |
Return the length of a string in bytes |
LOCATE() |
Return the position of the first occurrence of substring |
LOWER() |
Return the argument in lowercase |
LTRIM() |
Remove leading spaces |
MID() |
Return a substring starting from the specified position |
NOT LIKE |
Negation of simple pattern matching |
NOT REGEXP |
Negation of REGEXP |
REGEXP |
Pattern matching using regular expressions |
REPLACE() |
Replace occurrences of a specified string |
RIGHT() |
Return the specified rightmost number of characters |
RTRIM() |
Remove trailing spaces |
SPACE() |
Return a string of the specified number of spaces |
SUBSTR() |
Return the substring as specified |
SUBSTRING() |
Return the substring as specified |
SUBSTRING_INDEX() |
Return a substring from a string before the specified number of
occurrences of the delimiter |
TRIM() |
Remove leading and trailing spaces |
UCASE() |
Synonym for UPPER() |
UPPER() |
Convert to uppercase |
Numeric Functions and Operators
Function/Operator |
Description |
ABS() |
Return the absolute value |
ACOS() |
Return the arc cosine |
ASIN() |
Return the arc sine |
ATAN() |
Return the arc tangent |
ATAN2(), ATAN() |
Return the arc tangent of the two arguments |
CEIL() |
Return the smallest integer value not less than the argument |
CEILING() |
Return the smallest integer value not less than the argument |
COS() |
Return the cosine |
COT() |
Return the cotangent |
DEGREES() |
Convert radians to degrees |
DIV |
Integer division |
/ |
Division operator |
EXP() |
Raise to the power of |
FLOOR() |
Return the largest integer value not greater than the argument |
LN() |
Return the natural logarithm of the argument |
LOG() |
Return the natural logarithm of the first argument |
LOG10() |
Return the base-10 logarithm of the argument |
LOG2() |
Return the base-2 logarithm of the argument |
- |
Minus operator |
MOD() |
Return the remainder |
%, MOD |
Modulo operator |
PI() |
Return the value of pi |
+ |
Addition operator |
POW() |
Return the argument raised to the specified power |
POWER() |
Return the argument raised to the specified power |
RADIANS() |
Return argument converted to radians |
ROUND() |
Round the argument |
SIGN() |
Return the sign of the argument |
SIN() |
Return the sine of the argument |
SQRT() |
Return the square root of the argument |
TAN() |
Return the tangent of the argument |
* |
Multiplication operator |
TRUNCATE() |
Truncate to specified number of decimal places |
- |
Change the sign of the argument |
Date and Time Functions
Function/Operator |
Description |
ADDDATE() |
Add time values (intervals) to a date value |
CURDATE() |
Return the current date |
CURRENT_DATE(), CURRENT_DATE |
Synonyms for CURDATE() |
CURRENT_TIMESTAMP(), CURRENT_TIMESTAMP |
Synonyms for NOW() |
DATE() |
Extract the date part of a date or datetime expression |
DATE_ADD() |
Add time values (intervals) to a date value |
DATE_FORMAT() |
Format date as specified |
DATE_SUB() |
Subtract a time value (interval) from a date |
DAY() |
Synonym for DAYOFMONTH() |
DAYNAME() |
Return the name of the weekday |
DAYOFMONTH() |
Return the day of the month (0-31) |
DAYOFWEEK() |
Return the weekday index of the argument |
DAYOFYEAR() |
Return the day of the year (1-366) |
EXTRACT() |
Extract part of a date |
FROM_DAYS() |
Convert a day number to a date |
HOUR() |
Extract the hour |
LAST_DAY |
Return the last day of the month for the argument |
MAKEDATE() |
Create a date from the year and day of year |
MINUTE() |
Return the minute from the argument |
MONTH() |
Return the month from the date passed |
MONTHNAME() |
Return the name of the month |
NOW() |
Return the current date and time |
QUARTER() |
Return the quarter from a date argument |
SECOND() |
Return the second (0-59) |
STR_TO_DATE() |
Convert a string to a date |
SUBDATE() |
Synonym for DATE_SUB() when invoked with three arguments |
TIME_TO_SEC() |
Return the argument converted to seconds |
TIMEDIFF() |
Subtract time |
TIMESTAMP() |
With a single argument, this function returns the date or datetime |
TIMESTAMPADD() |
Add an interval to a datetime expression |
TIMESTAMPDIFF() |
Subtract an interval from a datetime expression |
TO_DAYS() |
Return the date argument converted to days |
UTC_TIMESTAMP() |
Return the current UTC date and time |
WEEK() |
Return the week number |
WEEKDAY() |
Return the weekday index |
WEEKOFYEAR() |
Return the calendar week of the date (1-53) |
YEAR() |
Return the year |
YEARWEEK() |
Return the year and week |
Cast Functions and Operators
Function |
Description |
CAST() |
Cast a value as a certain type |
CONVERT() |
Convert a value as a certain type |
GROUP BY (Aggregate) Functions
Function |
Description |
AVG() |
Return the average value of the argument |
COUNT() |
Return a count of the number of rows returned |
COUNT(DISTINCT) |
Return the count of a number of different values |
MAX() |
Return the maximum value |
MIN() |
Return the minimum value |
STD() |
Return the population standard deviation |
STDDEV() |
Return the population standard deviation |
STDDEV_POP() |
Return the population standard deviation |
STDDEV_SAMP() |
Return the sample standard deviation |
SUM() |
Return the sum |