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	AWS SCT / AWS DMS automation level	AWS 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.