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
This version of the documentation is archived and no longer supported. View the current documentation instead.
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 | |
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
ExampleReturns | |
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. |