Docs Menu

Supported SQL Functions and Operators

On this page

The following tables list the various MySQL functions, constructors, and operators supported in MongoDB Connector for BI 2.2.

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

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

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
Function/Operator
Description
AND, &&
Logical AND
NOT, !
Negates value
||, OR
Logical OR
XOR
Logical XOR
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.
Function/Operator
Description
CASE
Case operator
IF()
If/else construct
IFNULL()
Null if/else construct
NULLIF()
Return NULL if expr1 = expr2
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
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
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
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.

Function
Description
ROW()
Returns one row of values rather than one column of values
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.
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
Statement
Description
USE <databaseName>
Choose the database to use as the current database for subsequent 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.
←  System VariablesFAQ: The MongoDB Connector for BI →
Give Feedback
© 2022 MongoDB, Inc.

About

  • Careers
  • Investor Relations
  • Legal Notices
  • Privacy Notices
  • Security Information
  • Trust Center
© 2022 MongoDB, Inc.