Single-row and aggregate Oracle and MySQL functions

Single-row and aggregate functions are essential SQL constructs that perform operations on individual rows or groups of rows, respectively. The following sections compare Oracle and MySQL single-row and aggregate functions.

Feature compatibility	Amazon SCT / Amazon DMS automation level	Amazon SCT action code index	Key differences
		N/A	MySQL doesn’t support all functions. These unsupported functions require manual creation.

Oracle usage

Oracle provides two main categories of built-in SQL functions based on the number of rows used as input and generated as output.

Single-row or scalar functions return a single result for each row of the queried table or view. You can use them with a SELECT statement in the WHERE clause, the START WITH clause, the CONNECT BY clause, and the HAVING clause. The single-row functions are divided into groups according to data types such as NUMERIC functions, CHAR functions, and DATETIME functions.
Aggregative or group functions are used to summarize a group of values into a single result. Examples include AVG, MIN, MAX, SUM, COUNT, LISTAGG, FIRST, and LAST.

See the following section for a comparison of Oracle and MySQL single-row functions.

Oracle 19 adds ability to eliminate duplicate items in LISTAGG function results with new DISTINCT keyword.

Oracle 19 introduces several new bitmap SQL aggregate functions such as BITMAP_BUCKET_NUMBER, BITMAP_BIT_POSITION and BITMAP_CONSTRUCT_AGG. These functions help speed up COUNT DISTINCT operations.

For more information, see Single-Row Functions and Aggregate Functions in Oracle documentation.

MySQL usage

MySQL provides an extensive list of single-row and aggregation functions. Some are similar to their Oracle counterparts by name and functionality, or under a different name but with similar functionality. Other functions can have identical names to their Oracle counterparts, but exhibit different functionality. In the following tables, the Equivalent column indicates functional equivalency.

Numeric functions

Oracle function and definition	MySQL function and definition	Equivalent
`ABS` — Absolute value of n: `abs (-11.3) = 11.3`.	`ABS` — Absolute value of n: `abs (-11.3) = 11.3`.	Yes
`CEIL` — Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) = -24`.	`CEIL` — Returns the smallest integer that is greater than or equal to n: `ceil (-24.9) = -24`.	Yes
`FLOOR` — Returns the largest integer equal to or less than n: `floor (-43.7) = -44`.	`FLOOR` — Returns the largest integer equal to or less than n: `floor (-43.7) = -44`.	Yes
`MOD` — Remainder of n2 divided by n1: `mod(10,3) = 1`.	`MOD` — Remainder of n2 divided by n1: `mod(10,3) = 1`.	Yes
`ROUND` — Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) = 3.5`.	`ROUND` — Returns n rounded to integer places to the right of the decimal point: `round (3.49, 1) = 3.5`.	Yes
`TRUNC` — Returns n1 truncated to n2 decimal places: `trunc(13.5) = 13`.	`TRUNCATE` — Returns n1 truncated to n2 decimal places: `trunc(13.5) = 13`.	Yes

Character functions

Oracle function and definition	MySQL function and definition	Equivalent
`CONCAT` — Returns char1 concatenated with char2: `concat('a', 1) → a1`.	`CONCAT` — Returns char1 concatenated with char2: `concat('a', 1) → a1`.	Yes
`LOWER` and `UPPER` — Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.	`LOWER` and `UPPER` — Returns char, with all letters lowercase or uppercase: `lower ('MR. Smith') → mr. smith`.	Yes
`LPAD` and `RPAD` — Returns `expr1`, left or right padded to length n characters with the sequence of characters in `expr2`: `LPAD('Log-1',10,'-') → -----Log-1`.	`LPAD` and `RPAD` — Returns `expr1`, left or right padded to length n characters with the sequence of characters in `expr2`: `LPAD('Log-1',10,'-') → -----Log-1`.	Yes
`REGEXP_REPLACE` — Search a string for a regular expression pattern: `regexp_replace('John', '[hn].', '1') → Jo1`.	You can simulate Oracle `REGEXP_REPLACE` function using MySQL built-in function.	No
`REGEXP_SUBSTR` — Extends the functionality of the `SUBSTR` function by searching a string for a regular expression pattern: REGEXP_SUBSTR('http://www.aws.-com/products', 'http://([[:alnum:]]+\.?){3,4}/?') `→ http://www.aws.com/`.	You can simulate Oracle `REGEXP_SUBSTR` function using MySQL built-in function.	No
`REPLACE` — Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.	`REPLACE` — Returns char with every occurrence of search string replaced with a replacement string: `replace ('abcdef', 'abc', '123') → 123def`.	Yes
`LTRIM` and `RTRIM` — Removes from the left or right end of char all of the characters that appear in set: `ltrim ('zzzyaws', 'xyz') → aws`.	`LTRIM` and `RTRIM` — Removes spaces from the left or right end of char: `ltrim(' Amazon') → Amazon`. Combine with the `REPLACE` function to get the results similar to Oracle.	Partly
`SUBSTR` — Returns a portion of char, beginning at character position, substring length characters long: `substr('John Smith', 6 ,1) → S`.	`SUBSTR` — Returns a portion of char, beginning at character position, substring length characters long: `substr('John Smith', 6 ,1) → S`.	Yes
`TRIM` — Trim leading or trailing characters or both from a character string: `trim (both 'x' FROM 'xJohnxx') → John`.	`TRIM` — Trim leading or trailing characters or both from a character string: `trim (both 'x' FROM 'xJohnxx') → John`.	Yes
`ASCII` — Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.	`ASCII` — Returns the decimal representation in the database character set of the first character of char: `ascii('a') → 97`.	Yes
`INSTR` — Search string for substring.	`INSTR` — Search string for substring.	Yes
`LENGTH` — Returns the length of char: `length ('John S.') → 7`.	`LENGTH` — Returns the length of char: `length ('John S.') → 7`.	Yes
`REGEXP_COUNT` — Returns the number of times, a pattern occurs in a source string.	You can simulate Oracle `REGEXP_COUNT` function using MySQL built-in function.	No
`REGEXP_INSTR` — Searches a string position for a regular expression pattern.	You can simulate Oracle `REGEXP_INSTR` function using MySQL built-in function.	No

Date and time functions

Oracle function and definition	MySQL function and definition	Equivalent
`ADD_MONTHS` — Returns the date plus integer months: `add_months( sysdate,1)`	`ADDDATE` — MySQL can implement the same functionality using the `ADDDATE` function.	No
`CURRENT_DATE` — Returns the current date in the session time zone: `select current_date from dual → 2017-01-01 13:01:01`.	`CURRENT_DATE` — Returns date without time. Use the `now()` or the `current_timestamp` function to achieve the same results: `select now() → 2017-01-01 13:01:01`.	Partly
`CURRENT_TIMESTAMP` — Returns the current date and time in the session time zone: `select current timestamp from dual; → 2017-01-01 13:01:01`.	`CURRENT_TIMESTAMP`— Returns the current date and time in the session time zone: `select current timestamp from dual; → 2017-01-01 13:01:01`.	Yes
`EXTRACT (date part)` — Returns the value of a specified date time field from a date time or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.	`EXTRACT (date part)` — Returns the value of a specified date time field from a date time or interval expression: `EXTRACT (YEAR FROM DATE '2017-03-07') → 2017`.	Yes
`LAST_DAY` — Returns the date of the last day of the month that contains date: `LAST_DAY('05-07-2018') → 05-31-2018`.	`LAST_DAY` — Returns the date of the last day of the month that contains date: `LAST_DAY('05-07-2018') → 05-31-2018`.	Yes
`BETWEEN` — Returns the number of months between dates date1 and date2: `MONTHS_BETWEEN ( sysdate, sysdate-100) → 3.25`.	`PERIOD_DIFF` — Returns the number of months between periods P1 and P2. P1 and P2 should be in the format `YYMM` or `YYYYMM`: `SELECT PERIOD_DIFF(201801,201703) → 10`	Partly
`SYSDATE` — Returns the current date and time set for the operating system on which the database server resides: `select sysdate from dual → 2017-01-01 13:01:01`.	`SYSDATE` — Returns the current date and time set for the operating system on which the database server resides: `select sysdate() → 2017-01-01 13:01:01`.	Yes
`SYSTIMESTAMP` — Returns the system date, including fractional seconds and time zone: `select systimestamp from dual → 2017-01-01 13:01:01.123456 PM+00:00`.	`CURRENT_TIMESTAMP` — Returns the current date and time in the session time zone: `select current timestamp from dual; → 2017-01-0113:01:01.123456+00`.	Yes
`LOCALTIMESTAMP` — Returns the current date and time in the session time zone in a value of the `TIMESTAMP` data type: `select localtimestamp from dual → 01-JAN-17 10.01.10.123456 PM`.	`LOCALTIMESTAMP` — Returns the current date and time in the session time zone in a value of the `TIMESTAMP` data type: `select localtimestamp from dual → 01-JAN-17 10.01.10.123456 PM`.	Yes
`TO_CHAR(datetime)` — Converts a date time or timestamp to data type to a value of `VARCHAR2` data type in the format specified by the date format: `to_char(sys-date, 'DD-MON-YYYY HH24:MI:SS') → 01-JAN-2017 01:01:01`.	`DATE_FORMAT` — Changes the format of the date and time: `DATE_FORMAT (SYSDATE(), '%Y-%m-%d %H:%i:%s')`	Yes
`TRUNC (date)` — Returns a date with the time portion of the day truncated to the unit specified by the format model: `trunc(systimestamp) → 2017-01-01 00:00:00`.	You can simulate Oracle `TRUNC` function using MySQL built-in function.	No

Encoding and decoding functions

Oracle function and definition	MySQL function and definition	Equivalent
`DECODE` — Compares an expression to each search value one by one using the functionality of an `IF-THEN-ELSE` statement.	`CASE` — Compares an expression to each search value one by one.	No
`DUMP` — Returns a `VARCHAR2` value containing the data type code, length in bytes, and internal representation of expression.	N/A	No
`ORA_HASH` — Computes a hash value for a given expression.	`SHA` — Calculates an SHA-1 160-bit checksum for the string.	No

Null functions

Oracle function and definition	MySQL function and definition	Equivalent
`CASE` — Chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …] [ELSE result] END`.	`CASE` — Chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …] [ELSE result] END`.	Yes
`COALESCE` — Returns the first non-null expr in the expression list: `coalesce (null, 'a', 'b') → a`.	`COALESCE` — Returns the first of its arguments that isn’t null: `coalesce (null, 'a', 'b') → a`.	Yes
`NULLIF` — Compares `expr1` and `expr2`. If they are equal, the function returns null. If they aren’t equal, the function returns `expr1`: `NULLIF('a', 'b') → a`.	`NULLIF` — Compares `expr1` and `expr2`. If they are equal, the function returns null. If they aren’t equal, the function returns `expr1`: `NULLIF('a', 'b') → a`.	Yes
`NVL` — Replaces null (returned as a blank) with a string in the results of a query: `NVL (null, 'a') → a`.	`IFNULL` — Replaces null (returned as a blank) with a string in the results of a query: `IFNULL (null, 'a') → a`.	No
`NVL2` — Determines the value returned by a query based on whether a specified expression is null or not null.	`CASE` — Chooses from a sequence of conditions and runs a corresponding statement: `CASE WHEN condition THEN result [WHEN …] [ELSE result] END`.	No

Environment and identifier functions

Oracle function and definition	MySQL function and definition	Equivalent
`SYS_GUID` — Generates and returns a globally unique identifier (RAW value) made up of 16 bytes: `select sys_guid() from dual → 5A280ABA8C76201EE0530-100007FF691`.	`UUID` and `REPLACE` — `REPLACE(UUID(), '-', '')`.	No
`UID` — Returns an integer that uniquely identifies the session user (the user who logged on): `select uid from dual → 84`.	N/A	No
`USER` — Returns the name of the session user: `select user from dual`.	`USER` — Returns the name of the session user and source machine: `select USER()`.	No
`USERENV` — Returns information about the current session using parameters: `SELECT USERENV ('LANGUAGE') "Language" FROM DUAL`.	`SHOW SESSION VARIABLES` — Displays the system variable values that are in effect for the current connection: `myshow SESSION VARIABLES LIKE 'collation_connection';`.	No

Oracle conversion functions

Oracle function and definition	MySQL function and definition	Equivalent
`CAST` — Converts one built-in data type or collection-typed value into another built-in data type or collection-typed value: `cast ('10' as int) + 1 → 11`.	`CAST` — Converts one built-in data type or collection-typed value into another built-in data type or collection-typed value: `cast ('10' as UNSIGNED) + 1`.	Yes
`CONVERT` — Converts a character string from a one-character set to another: `select convert ('Ä Ê Í Õ Ø A B C D E ', 'US7ASCII', 'WE8ISO8859P1') from dual`.	`CONVERT` — Converts a character string from a one-character set to another: `select convert ('Ä Ê Í Õ Ø A B C D E ' USING utf8)`.	Yes
`TO_CHAR (string / numeric)` — Converts `NCHAR`, `NVARCHAR2`, `CLOB`, or `NCLOB` data to the database character set: `select to_char ('01234') from dual → 01234`.	`FORMAT` — Converts string data to the database character set: `FORMAT('01234', 0) -→ 01234`.	No
`TO_DATE` — Converts char of `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2` data type to a value of `DATE` data type: `to_date('01Jan2017','DDMonYYYY') → 01-JAN-17`.	`STR_TO_DATE` — Convert string data type to a value of `DATE` data type: `SELECT STR_TO_DATE('01Jan2017','%d%M%Y')`.	No
`TO_NUMBER` — Converts an expression to a value of `NUMBER` data type: `to_number('01234') → 1234 or to_number('01234', '99999') → 1234`.	N/A	No

Aggregate functions

Oracle function and definition	MySQL function and definition	Equivalent
`AVG` — Returns an average value of an expression: `select avg(salary) from employees`.	`AVG` — Returns an average value of an expression: `select avg(salary) from employees`.	Yes
`COUNT` — Returns the number of rows returned by the query: `select count(*) from employees`.	`COUNT` — Returns the number of rows returned by the query: `select count(*) from employees`.	Yes
`LISTAGG` — Orders data within each group specified in the `ORDER BY` clause and then concatenates the values of the measure column: `select listagg(firstname,' ,') within group (order by customerid) from customer`.	`GROUP_CONCAT` — Orders data within each group specified in the `ORDER BY` clause and then concatenates the values of the measure column: `select GROUP_CONCAT(firstname order by customerid) from customer`.	No
`MAX` — Returns the maximum value of an expression: `select max(salary) from employees`.	`MAX` — Returns the maximum value of an expression: `select max(salary) from employees`.	Yes
`MIN` — Returns the minimum value of an expression: `select min(salary) from employees`.	`MIN` — Returns the minimum value of an expression: `select min(salary) from employees`.	Yes
`SUM` — Returns the sum of values of an expression: `select sum(salary) from employees`.	`SUM` — Returns the sum of values of an expression: `select sum(salary) from employees`.	Yes

Top-N Query Oracle 12c

Oracle function and definition	MySQL function and definition	Equivalent
`FETCH` — Retrieves rows of data from the result set of a multi-row query: `select * from customer fetch first 10 rows only`.	`LIMIT` — Retrieves just a portion of the rows that are generated by the rest of the query: `select * from customer LIMIT 10`.	Yes

For more information, see String Functions and Operators and Numeric Functions and Operators in the MySQL documentation.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

SQL and PL/SQL

Oracle and MySQL CREATE TABLE AS SELECT statement