MySQL Single-Row Functions — Numeric, String & Date Functions (2026)

Introduction#

Why This Matters#

Single-row functions (also called scalar functions) transform or extract information from individual column values in every row. They appear in SELECT lists, WHERE conditions, ORDER BY clauses, and UPDATE statements. Knowing the right function saves dozens of lines of application code and lets the database engine do the heavy lifting efficiently.

Unlike aggregate functions that summarise multiple rows into one, each single-row function returns one output value for every input row — hence the name.

Numeric Functions#

POWER / POW — Exponentiation#

POWER(x, y) or POW(x, y) returns x raised to the power y.

SELECT POW(3, 2);    -- Output: 9
SELECT POWER(2, 10); -- Output: 1024
SELECT POW(4, 0.5);  -- Output: 2  (square root)

ROUND — Rounding Numbers#

ROUND(x) rounds to the nearest integer. ROUND(x, d) rounds to d decimal places. Negative d rounds to tens, hundreds, etc.

SELECT ROUND(3878.6546);     -- Output: 3879
SELECT ROUND(3878.6546, 1);  -- Output: 3878.7
SELECT ROUND(3878.6546, 2);  -- Output: 3878.65
SELECT ROUND(3878.6546, 3);  -- Output: 3878.655
SELECT ROUND(3878.6546, -1); -- Output: 3880
SELECT ROUND(3878.6546, -2); -- Output: 3900

TRUNCATE — Cutting Off Digits#

TRUNCATE(x, d) cuts off digits beyond d decimal places without rounding. Unlike ROUND, it never rounds up.

SELECT TRUNCATE(3878.6546, 0);  -- Output: 3878
SELECT TRUNCATE(3878.6546, 1);  -- Output: 3878.6
SELECT TRUNCATE(3878.6546, 2);  -- Output: 3878.65
SELECT TRUNCATE(3878.6546, 3);  -- Output: 3878.654
SELECT TRUNCATE(3878.6546, -1); -- Output: 3870
SELECT TRUNCATE(3878.6546, -2); -- Output: 3800

ROUND(3878.6546, 2) = 3878.65, but TRUNCATE(3878.6546, 2) = 3878.65 as well here — the difference appears when the third digit is 5 or higher: ROUND(3.555, 2) = 3.56, TRUNCATE(3.555, 2) = 3.55.

MOD — Remainder#

SELECT MOD(10, 3); -- Output: 1
SELECT MOD(15, 5); -- Output: 0

ABS — Absolute Value#

SELECT ABS(-250);  -- Output: 250
SELECT ABS(3.14);  -- Output: 3.14

String Functions#

LENGTH — String Length#

LENGTH(str) returns the number of bytes. For ASCII strings this equals the character count; for multibyte characters (utf8mb4) use CHAR_LENGTH.

SELECT LENGTH('Informatics Practices');  -- Output: 21
SELECT CHAR_LENGTH('café');              -- Output: 4 (characters)
SELECT LENGTH('café');                   -- Output: 5 (bytes in utf8)

CONCAT — Joining Strings#

CONCAT(str1, str2, ...) joins multiple strings into one. If any argument is NULL, the result is NULL. Use CONCAT_WS(separator, ...) to insert a separator between each value.

SELECT CONCAT('Hello', ' ', 'World');         -- Output: Hello World
SELECT CONCAT_WS(', ', 'Alice', 'Bob', 'Eve'); -- Output: Alice, Bob, Eve

INSTR — Finding a Substring#

INSTR(str, substr) returns the position (1-based) of the first occurrence of substr in str, or 0 if not found.

SELECT INSTR('Informatics Information', 'Inform'); -- Output: 1
SELECT INSTR('Hello World', 'World');              -- Output: 7
SELECT INSTR('Hello World', 'xyz');               -- Output: 0

LOWER / LCASE and UPPER / UCASE#

SELECT LOWER('Informatics Practices');  -- Output: informatics practices
SELECT LCASE('HELLO');                  -- Output: hello
 
SELECT UPPER('informatics practices');  -- Output: INFORMATICS PRACTICES
SELECT UCASE('hello');                  -- Output: HELLO

LEFT and RIGHT — Extracting from Ends#

SELECT LEFT('I Love SQL', 6);   -- Output: I Love
SELECT RIGHT('I Love SQL', 3);  -- Output: SQL

LTRIM, RTRIM, TRIM — Removing Spaces#

SELECT LTRIM('   Hello World   ');  -- Output: 'Hello World   '
SELECT RTRIM('   Hello World   ');  -- Output: '   Hello World'
SELECT TRIM('   Hello World   ');   -- Output: 'Hello World'

TRIM also accepts a character to trim:

SELECT TRIM('x' FROM 'xxxHelloWorldxxx'); -- Output: HelloWorld

SUBSTRING / SUBSTR / MID — Extracting a Portion#

SUBSTRING(str, m, n) returns n characters starting from position m (1-based). Negative m counts from the end of the string. If n is omitted, the rest of the string is returned.

SELECT SUBSTRING('I Love SQL', 3, 4);  -- Output: Love
SELECT SUBSTRING('I Love SQL', 3);     -- Output: Love SQL
SELECT SUBSTR('I Love SQL', -3, 3);    -- Output: SQL  (last 3 chars)
SELECT MID('I Love SQL', 8, 3);        -- Output: SQL

REPLACE — Substituting Text#

SELECT REPLACE('Hello World', 'World', 'MySQL'); -- Output: Hello MySQL

Date Functions#

CURDATE — Current Date#

CURDATE() returns the current date in YYYY-MM-DD format. It does not include the time component.

SELECT CURDATE(); -- Output: 2026-03-19

NOW and SYSDATE — Current Date and Time#

NOW() returns the date and time when the statement began executing — it stays constant within a single query. SYSDATE() returns the exact time of function evaluation, which can differ from NOW() inside stored procedures with delays.

SELECT NOW();      -- Output: 2026-03-19 14:30:00
SELECT SYSDATE();  -- Output: 2026-03-19 14:30:00 (may differ inside loops)

DATE — Extract Date from Datetime#

SELECT DATE('2026-03-19 14:30:00'); -- Output: 2026-03-19

YEAR, MONTH, DAY — Extract Components#

SELECT YEAR('2026-03-19');  -- Output: 2026
SELECT MONTH('2026-03-19'); -- Output: 3
SELECT DAY('2026-03-19');   -- Output: 19

DAYNAME — Name of the Day#

SELECT DAYNAME('2026-03-19');   -- Output: Thursday
SELECT DAYNAME(CURDATE());      -- returns today's day name

DAYOFMONTH, DAYOFWEEK, DAYOFYEAR#

SELECT DAYOFMONTH('2026-03-19'); -- Output: 19
SELECT DAYOFWEEK('2026-03-19');  -- Output: 5  (1=Sunday, 2=Monday...)
SELECT DAYOFYEAR('2026-03-19');  -- Output: 78

Practical Examples — Functions in Queries#

-- Display names in uppercase with trimmed fees formatted to 2 decimal places
SELECT UPPER(name), ROUND(fees, 2)
FROM students;
 
-- Find students whose name starts at position 1 with 'A'
SELECT name FROM students WHERE INSTR(name, 'A') = 1;
 
-- Age in years from date of birth
SELECT name, YEAR(CURDATE()) - YEAR(dob) AS age FROM students;
 
-- Format a concatenated full address
SELECT CONCAT_WS(', ', city, state, country) AS full_address FROM addresses;

Common Mistakes#

• Using LENGTH instead of CHAR_LENGTH for multibyte characters. LENGTH counts bytes; CHAR_LENGTH counts characters.
• Forgetting that CONCAT returns NULL if any argument is NULL. Use CONCAT_WS or IFNULL(column, '') to guard against NULLs.
• Mixing up ROUND and TRUNCATE. ROUND rounds to nearest; TRUNCATE always cuts toward zero.
• Using NOW() when you want only the date. Use CURDATE() for date-only comparisons to avoid time-of-day issues.
• Assuming INSTR is zero-based. It is 1-based like all MySQL string position functions.
• DAYOFWEEK returns 1 for Sunday (not Monday) — remember this when filtering by day.

Best Practices#

• Use CHAR_LENGTH over LENGTH when working with utf8mb4 columns.
• Wrap nullable string columns in IFNULL(column, '') before passing to CONCAT.
• Use YEAR(column) = 2026 carefully — it prevents index use; prefer range conditions column BETWEEN '2026-01-01' AND '2026-12-31'.
• Store dates as DATE or DATETIME columns rather than strings — this allows date functions and comparisons to work correctly.
• Use CONCAT_WS when building delimited lists to avoid NULL propagation issues.

Key Takeaways#

• Single-row functions return one output value per input row, unlike aggregate functions that collapse multiple rows into one.
• ROUND(x, d) rounds to d decimal places; TRUNCATE(x, d) cuts digits beyond d without rounding.
• CONCAT(str1, str2) joins strings; if any argument is NULL the result is NULL — use CONCAT_WS to avoid this.
• INSTR(str, substr) returns a 1-based position, or 0 if the substring is not found.
• SUBSTRING(str, m, n) extracts n characters from position m; negative m counts from the end of the string.
• CURDATE() returns only the date; NOW() returns date and time; SYSDATE() reflects the actual execution moment.
• YEAR(), MONTH(), DAY(), DAYNAME(), and DAYOFWEEK() extract specific components from a date column.
• CHAR_LENGTH(str) counts characters; LENGTH(str) counts bytes — they differ for multibyte (utf8mb4) characters.