Supported SQL Functions and Operators
On this page
- Comparison Functions and Operators
- Logical Operators
- JOIN Expressions
- Control Flow Functions and Operators
- String Functions
- Numeric Functions and Operators
- Date and Time Functions
- Cast Functions and Operators
- Subquery Constructors
- Information Functions
GROUP BY(Aggregate) Functions- Utility Statements
- Database Administration Statements
The following tables list the various MySQL functions, constructors, and operators supported in MongoDB Connector for BI 2.2.
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 | |
EXISTS() | Test for the existence of a record you specify in a subquery | |
IN() | Check whether a value is within a set of values | |
INTERVAL(N,N1,N2,...) | Return the 0-based index position of last number in the list to be
For example: Returns | |
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 |
JOIN Expressions
Expression | Description |
|---|---|
JOIN | Select records that have matching values in multiple tables. |
INNER JOIN | Semantically equivalent to JOIN. |
LEFT JOIN | Return all records from the left table, and the matched records
from the right table. |
RIGHT JOIN | Return all records from the right table, and the matched records
from the left table. |
NATURAL JOIN | Return only records which do not appear in both tables. |
USING | Return all records where the given columns appear in both tables. |
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 |
DATEDIFF() | Returns the difference, in days, between two specified dates. |
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 |
MICROSECOND() | Return the microseconds from the input time or datetime expression
as a number between 0 and 999999 inclusive. |
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 |
For more information on how the BI Connector performs type conversions, see Type Conversion Modes.
Subquery Constructors
Function | Description |
|---|---|
ROW() | Returns one row of values rather than one column of values |
Information Functions
Function | Description |
|---|---|
CONNECTION_ID() | Return the connection ID (thread ID) for the connection |
CURRENT_USER(), CURRENT_USER | The authenticated user name and host name |
DATABASE() | Return the default (current) database name |
SCHEMA() | Synonym for DATABASE() |
SESSION_USER() | Synonym for USER() |
SYSTEM_USER() | Synonym for USER() |
USER() | The user name and host name provided by the client |
VERSION() | Return a string that indicates the MySQL server version |
EXPLAIN() | Return information about an SQL query without running it or fetching
results. Includes the full aggregation operation which BI Connector will send to MongoDB. |
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 |
GROUP_CONCAT() | Return a concatenated string, non- NULL values only |
Utility Statements
Statement | Description |
|---|---|
USE <databaseName> | Choose the database to use as the current database for subsequent statements |
Database Administration Statements
Statement | Description |
|---|---|
SHOW CREATE TABLE <tableName> | Shows the structure of the <tableName> table. The structure
includes comments that describe the mapping from the SQL table and
columns to MongoDB collection and fields. |
SHOW [GLOBAL | SESSION] STATUS [LIKE 'pattern' | WHERE expr] | Shows
By default the |
ALTER TABLE <tableName> [alter_specification [, alter_specification] ...] | Use to:
|
RENAME TABLE <tableName> TO <newTableName> [, <tableName2> TO <newTableName2> ] ... | Use to rename one or more tables. |