Single-Row and Aggregate Functions - Oracle to Aurora MySQL Migration Playbook
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Single-Row and Aggregate Functions

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Four star feature compatibility

Four star automation level

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 REPLACEREPLACE(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

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.