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 < N. Return -1 if N is NULL

N1, N2, etc. must be given in ascending order.

For example:

SELECT INTERVAL(26, 2, 9, 12, 28.5, 62, 2300);

Returns 3 because 12 is the last number in the list less than 26.

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 `mongosqld` server metrics. The structure contains non-empty values for at least the following fields: `Bytes_received` `Bytes_sent` `Connections` `Queries` `Threads_connected` `Threads_created` `Uptime` By default the `SHOW STATUS` statement will only show statistics for the current connection, corresponding to the `SESSION` qualifier. If you specify the `GLOBAL` qualifier, `SHOW STATUS` will instead show aggregate statistics for the entire server.
`ALTER TABLE <tableName> [alter_specification [, alter_specification] ...]`	Use to: Rename tables Add, update or remove columns in a table Change the type of a column in a table
`RENAME TABLE <tableName> TO <newTableName> [, <tableName2> TO <newTableName2> ] ...`	Use to rename one or more tables.

Back

Known Issues for MongoDB Connector for BI

FAQ