MySQL Functions Reference

MySQL provides a rich set of built-in functions for string manipulation, numeric calculations, date/time handling, and aggregate processing. While many functions adhere to standard ANSI SQL, several syntax rules and function names differ significantly across major vendors (PostgreSQL, SQL Server, and Oracle).

1. String Functions

Function	Description & MySQL Example	Vendor Differences & Alternatives
`CONCAT()`	Joins two or more strings together. `SELECT CONCAT('MySQL', ' ', '8.0');`	SQL Server / Oracle: Supports `CONCAT()`. String concatenation operators differ by vendor. PostgreSQL: Uses `\|\|` (e.g., `'a' \|\| 'b'`).
`CONCAT_WS()`	Concatenates strings with a specified separator. `SELECT CONCAT_WS('-', '2026', '09', '27');`	PostgreSQL: Supported natively. SQL Server: Supported in SQL Server 2017+. Oracle: Common alternatives include `LISTAGG()` for row aggregation or concatenation operators for individual values.
`SUBSTRING()` / `SUBSTR()`	Extracts a substring starting at a position for a specified length. `SELECT SUBSTRING('Database', 1, 4);` — Output: `'Data'`	SQL Server: Uses `SUBSTRING()`. PostgreSQL / Oracle: Support `SUBSTR()`; PostgreSQL also supports `SUBSTRING()`.
`LENGTH()` / `CHAR_LENGTH()`	`LENGTH()` returns byte count; `CHAR_LENGTH()` returns character count. `SELECT CHAR_LENGTH('MySQL');`	SQL Server: Uses `LEN()` for character count, excluding trailing spaces. PostgreSQL: `LENGTH()` returns character count for text. Oracle: Uses `LENGTH()` for character count and `LENGTHB()` for bytes.
`REPLACE()`	Replaces all occurrences of a substring. `SELECT REPLACE('v1.0', '1.0', '2.0');`	Supported across MySQL, PostgreSQL, SQL Server, and Oracle.
`IFNULL()` / `NVL()`	Replaces `NULL` with a fallback value. `SELECT IFNULL(commission, 0);`	PostgreSQL / SQL Server: Common alternatives include `COALESCE()`; SQL Server also provides `ISNULL()`. Oracle: Uses `NVL()`. Best Practice: Use standard ANSI `COALESCE()` when portability is important.

2. Date & Time Functions

Function	Description & MySQL Example	Vendor Differences & Alternatives
`NOW()` / `CURRENT_TIMESTAMP()`	Returns the current date and time. `SELECT NOW();`	SQL Server: `GETDATE()` or `CURRENT_TIMESTAMP`. PostgreSQL / Oracle: `CURRENT_TIMESTAMP` is available; PostgreSQL also provides `clock_timestamp()` for the actual current time.
`DATE_ADD()` / `DATE_SUB()`	Adds or subtracts a time interval from a date. `SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);`	PostgreSQL: Uses interval arithmetic, such as `NOW() + INTERVAL '7 days'`. SQL Server: Uses `DATEADD(day, 7, GETDATE())`. Oracle: Supports direct date arithmetic, such as `SYSDATE + 7`.
`DATEDIFF()`	Calculates the difference in days between two date expressions. `SELECT DATEDIFF('2026-12-31', '2026-01-01');`	SQL Server: `DATEDIFF(day, start, end)` takes three arguments. PostgreSQL: Date subtraction can be used directly, such as `date1 - date2`.
`DATE_FORMAT()`	Formats a date value according to a format string. `SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');`	PostgreSQL: Uses `TO_CHAR(date, 'YYYY-MM-DD')`. SQL Server: Uses `FORMAT(date, 'yyyy-MM-dd')` or `CONVERT()`. Oracle: Uses `TO_CHAR(date, 'YYYY-MM-DD')`.

3. Numeric & Mathematical Functions

Function	Description & MySQL Example	Vendor Differences & Alternatives
`ROUND()`	Rounds a number to a specified number of decimal places. `SELECT ROUND(123.4567, 2);` — Output: `123.46`	Supported across the major RDBMS engines.
`TRUNCATE()`	Truncates a number to a specified decimal length without rounding. `SELECT TRUNCATE(123.4567, 2);` — Output: `123.45`	PostgreSQL: Uses `TRUNC()`. SQL Server: Can use `ROUND(value, length, 1)` for truncation. Oracle: Uses `TRUNC()`.
`RAND()`	Generates a random floating-point value between 0 and 1. `SELECT RAND();`	PostgreSQL: Uses `random()`. Oracle: Commonly uses `DBMS_RANDOM.VALUE`. SQL Server: Uses `RAND()`.
`MOD()`	Returns the remainder of a division operation. `SELECT MOD(10, 3);` — Output: `1`	Supported across major engines, with `%` also available in several dialects.

4. Control Flow Functions

Function	Description & MySQL Example	Vendor Differences & Alternatives
`IF()`	Evaluates a condition and returns a value based on the result. `SELECT IF(score >= 50, 'Pass', 'Fail');`	PostgreSQL / Oracle: Use a `CASE` expression in SQL queries. SQL Server: Provides `IIF(score >= 50, 'Pass', 'Fail')` as well as `CASE`.
`CASE` Expression	Provides standard conditional `IF-THEN-ELSE` logic. `SELECT CASE WHEN status = 1 THEN 'Active' ELSE 'Inactive' END;`	Standard SQL syntax supported across MySQL, PostgreSQL, SQL Server, and Oracle.
`NULLIF()`	Returns `NULL` if two expressions are equal; otherwise returns the first expression. `SELECT NULLIF(total, 0);`	Standard SQL syntax supported across the major RDBMS engines.

Vendor Dialect Comparison Matrix

Feature / Logic	MySQL	PostgreSQL	SQL Server (T-SQL)	Oracle
Result Limiting	`LIMIT 10 OFFSET 0`	`LIMIT 10 OFFSET 0`	`TOP (10)` or `FETCH FIRST 10 ROWS ONLY`	`FETCH FIRST 10 ROWS ONLY` or `WHERE ROWNUM <= 10`
String Concatenation	`CONCAT(a, b)`	`a \|\| b`	`a + b`	`a \|\| b`
Auto-Increment Primary Key	`AUTO_INCREMENT`	`SERIAL` / `GENERATED ALWAYS AS IDENTITY`	`IDENTITY(1,1)`	`GENERATED ALWAYS AS IDENTITY` or `SEQUENCE`
Current Date/Time	`NOW()`	`CURRENT_TIMESTAMP`	`GETDATE()`	`SYSDATE`
String Pattern Matching (Insensitive)	`LIKE` (case-insensitive under common case-insensitive collations)	`ILIKE`	`LIKE` (depends on database collation)	`REGEXP_LIKE` or `LOWER(col) LIKE`