Single Row Functions

What makes a function "single row"

A function that operates on one row at a time and returns one result per row, unlike an aggregate function (SUM, COUNT, ...) which collapses many rows down into one.

Single row functions split further by the kind of data they work on: LEFT below is a string function, YEAR is a date function, and MOD, POWER/POW, ROUND, SIGN, SQRT, and TRUNCATE are all math (numeric) functions.

LEFT()

select left("Ramesh", 3);

+-------------------+
| left("Ramesh", 3) |
+-------------------+
| Ram               |
+-------------------+
1 row in set (0.02 sec)

LEFT(string, n) returns the first n characters of string, counting from the left. LEFT("Ramesh", 3) takes the first 3 characters, "Ram".

YEAR()

select year(doj) from employee;

+-----------+
| year(doj) |
+-----------+
|      2020 |
|      2023 |
|      2026 |
|      2019 |
|      2022 |
|      2022 |
|      2020 |
|      2020 |
+-----------+
8 rows in set (0.02 sec)

YEAR(date) pulls just the year out of a DATE value, doj = '2020-12-23' returns 2020. Runs once per row, same as any single row function, employee has 8 rows, so 8 years come back, in the same order the rows were returned.

MOD()

select mod(4,2);

+----------+
| mod(4,2) |
+----------+
|        0 |
+----------+
1 row in set (0.03 sec)

select mod(9,2);

+----------+
| mod(9,2) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

MOD(n, m) returns the remainder of n / m. 4 / 2 divides evenly, remainder 0. 9 / 2 is 4 remainder 1, so MOD(9, 2) returns 1, the classic way to test odd/even: MOD(n, 2) = 0 means even, MOD(n, 2) = 1 means odd.

Dividing by zero doesn't error out:

select mod(4,0);

+----------+
| mod(4,0) |
+----------+
|     NULL |
+----------+
1 row in set, 1 warning (0.01 sec)

MOD(n, 0) is mathematically undefined, but MySQL doesn't throw a hard error for it, it returns NULL and raises a warning instead, same "safe by default" behavior as regular division by zero. Worth remembering since a NULL here can quietly disappear from later filtering (WHERE) or aggregation instead of surfacing loudly as an error.

POWER() / POW()

select power(2,3);

+------------+
| power(2,3) |
+------------+
|          8 |
+------------+
1 row in set (0.01 sec)

select pow(2,0);

+----------+
| pow(2,0) |
+----------+
|        1 |
+----------+
1 row in set (0.00 sec)

POWER(base, exponent) raises base to exponent, 2³ = 8. POW is the exact same function, just a shorter alias for the same name, both spellings work interchangeably. POW(2, 0) returning 1 is standard math: anything to the power of 0 is 1, MySQL follows that rule as expected.

ROUND()

select round(3.14159,3);

+-------------------+
| round(3.14159, 3) |
+-------------------+
|             3.142 |
+-------------------+
1 row in set (0.00 sec)

ROUND(number, decimals) rounds number to decimals digits after the decimal point. 3.14159 rounded to 3 places looks at the 4th digit (5) to decide whether to round up, 3.1415... rounds up to 3.142. Same shape as DECIMAL(10, 2) from Data Types, the second argument controls precision, just at query time instead of baked into a column's type.

0 decimals rounds to the nearest whole number:

select round(3.14159,0);

+-------------------+
| round(3.14159, 0) |
+-------------------+
|                  3 |
+-------------------+
1 row in set (0.00 sec)

A negative number moves in the other direction, rounding to the left of the decimal point instead of the right:

select round(3.14159,-1);

+--------------------+
| round(3.14159, -1) |
+--------------------+
|                  0 |
+--------------------+
1 row in set (0.00 sec)

select round(157.3232,-1);

+---------------------+
| round(157.3232, -1) |
+---------------------+
|                  160 |
+---------------------+
1 row in set (0.00 sec)

select round(157.3232,-2);

+---------------------+
| round(157.3232, -2) |
+---------------------+
|                  200 |
+---------------------+
1 row in set (0.00 sec)

-1 rounds to the nearest 10, -2 to the nearest 100, each step left of the decimal point instead of right. 157.3232 rounded to -1 looks at the ones digit (7) to decide whether the tens digit rounds up, 150 → 160. Rounded to -2, it looks at the tens digit (5) instead, 100 → 200.

SIGN()

select sign(-1), sign(0), sign(1);

+----------+---------+---------+
| sign(-1) | sign(0) | sign(1) |
+----------+---------+---------+
|       -1 |       0 |       1 |
+----------+---------+---------+
1 row in set (0.00 sec)

SIGN(number) reports which side of zero number is on, and nothing more: -1 for anything negative, 0 for exactly zero, 1 for anything positive. The actual magnitude never matters, SIGN(-500) and SIGN(-1) both return -1.

SQRT()

select sqrt(25), sqrt(26);

+----------+--------------------+
| sqrt(25) | sqrt(26)           |
+----------+--------------------+
|        5 | 5.0990195135927845 |
+----------+--------------------+
1 row in set (0.03 sec)

SQRT(number) returns the square root. 25 is a perfect square, so SQRT(25) comes back as a clean 5. 26 isn't, so SQRT(26) comes back as a long decimal, MySQL doesn't round it off on its own, pair it with ROUND() if a cleaner number is what's actually needed.

TRUNCATE()

select truncate(3.14159,4);

+----------------------+
| truncate(3.14159, 4) |
+----------------------+
|                3.1415 |
+----------------------+
1 row in set (0.00 sec)

TRUNCATE(number, decimals) cuts a number down to decimals digits, same second argument as ROUND, but it chops the extra digits off instead of rounding based on them. 3.14159 truncated to 4 places is 3.1415, the 9 just gets dropped, no check on whether it should round the 5 up to 6. ROUND(3.14159, 4) would return 3.1416 instead, that's the difference between the two.

CHAR()

Not the same CHAR as the data type in Data Types, this one's a function, different meaning entirely, and it's the reverse of ASCII().

select char(77, 121, 83, 81, 76);

+----------------------------+
| char(77, 121, 83, 81, 76)  |
+----------------------------+
| MySQL                      |
+----------------------------+
1 row in set (0.00 sec)

CHAR(n1, n2, ...) treats each number as an ASCII (character) code, and builds a string out of the character each one represents. 77 is 'M', 121 is 'y', 83 is 'S', 81 is 'Q', 76 is 'L', put together, 'MySQL'. Each argument becomes exactly one character in the result, in the order given.

CONCAT()

select concat("Ram", "Kumar", "Sharma");

+---------------------------------+
| concat("Ram", "Kumar", "Sharma") |
+---------------------------------+
| RamKumarSharma                   |
+---------------------------------+
1 row in set (0.00 sec)

CONCAT(str1, str2, ...) joins any number of strings together, end to end, with nothing inserted in between. "Ram" + "Kumar" + "Sharma" becomes "RamKumarSharma", no spaces added automatically, if a space or separator is wanted between the pieces, it has to be included as its own argument, concat("Ram", " ", "Kumar") for "Ram Kumar".

LOWER() / LCASE()

select lower("Ram");

+--------------+
| lower("Ram") |
+--------------+
| ram          |
+--------------+
1 row in set (0.01 sec)

select lcase("Ram");

+--------------+
| lcase("Ram") |
+--------------+
| ram          |
+--------------+
1 row in set (0.00 sec)

LOWER(string) converts every letter in string to lowercase. LCASE is the exact same function under a second name, same relationship as POWER/POW earlier, both spellings do exactly the same thing.

UCASE() / UPPER()

select ucase("Ram");

+--------------+
| ucase("Ram") |
+--------------+
| RAM          |
+--------------+
1 row in set (0.00 sec)

select upper("Ram");

+--------------+
| upper("Ram") |
+--------------+
| RAM          |
+--------------+
1 row in set (0.00 sec)

The opposite of LOWER/LCASE, converts every letter to uppercase instead. UCASE and UPPER are the same function under two names, exactly the same pairing as LOWER/LCASE, just going the other direction.

SUBSTR()

select substr("Anand",-4,4);

+------------------------+
| substr("Anand", -4, 4) |
+------------------------+
| nand                   |
+------------------------+
1 row in set (0.00 sec)

select substr("Anand",3,3);

+-----------------------+
| substr("Anand", 3, 3) |
+-----------------------+
| and                   |
+-----------------------+
1 row in set (0.00 sec)

SUBSTR(string, start, length) pulls out length characters starting at position start. Positions count from 1, not 0, substr("Anand", 3, 3) starts at the 3rd character ('a') and takes 3, "and".

A negative start counts from the end of the string instead of the beginning, -1 is the last character, -2 the one before it, and so on. substr("Anand", -4, 4) starts 4 characters from the end (the second 'n') and takes 4 characters forward from there, "nand".

LTRIM() / RTRIM()

select ltrim("     SQL");

+-------------------+
| ltrim("     SQL") |
+-------------------+
| SQL               |
+-------------------+
1 row in set (0.00 sec)

select rtrim("SQL     ");

+-------------------+
| rtrim("SQL     ")  |
+-------------------+
| SQL                |
+-------------------+
1 row in set (0.00 sec)

LTRIM(string) strips leading whitespace, spaces before the actual text, off the left side. RTRIM(string) does the same off the right, trailing whitespace after the text. Each only touches its own side, LTRIM leaves trailing spaces alone and RTRIM leaves leading ones alone, whichever side isn't named in the function stays exactly as it was.

TRIM()

select trim("     SQL     ");

+------------------------+
| trim("     SQL     ")  |
+------------------------+
| SQL                    |
+------------------------+
1 row in set (0.00 sec)

TRIM(string) strips both sides at once, effectively LTRIM and RTRIM combined into one call. Leading and trailing whitespace both get removed, whatever's in the middle is left untouched.

TRIM isn't limited to whitespace either, it can strip a specific character instead, and from just one side:

select trim(leading 'x' from "xxxSQLx");

+-------------------------------+
| trim(leading 'x' from "xxxSQLx") |
+-------------------------------+
| SQLx                           |
+-------------------------------+
1 row in set (0.00 sec)

select trim(trailing 'x' from "xxxSQLx");

+--------------------------------+
| trim(trailing 'x' from "xxxSQLx") |
+--------------------------------+
| xxxSQL                          |
+--------------------------------+
1 row in set (0.00 sec)

TRIM(LEADING 'x' FROM string) strips 'x' characters off only the front, leaving the trailing x in "xxxSQLx" untouched, "SQLx". TRIM(TRAILING 'x' FROM string) is the mirror, strips 'x' off only the end, "xxxSQL". Same LEADING/TRAILING split as LTRIM/RTRIM, just expressed as keywords inside TRIM instead of two separate function names, and with control over exactly which character gets stripped.

INSTR()

select instr("Anand", "nd");

+-----------------------+
| instr("Anand", "nd")  |
+-----------------------+
|                     4 |
+-----------------------+
1 row in set (0.00 sec)

INSTR(string, substring) returns the position where substring first appears inside string, counting from 1. "nd" first shows up starting at position 4 in "Anand" (A-n-a-n-d, the n at index 4). If the substring isn't found anywhere, INSTR returns 0, not -1 and not NULL.

LENGTH()

select length("Anand");

+-----------------+
| length("Anand") |
+-----------------+
|               5 |
+-----------------+
1 row in set (0.00 sec)

LENGTH(string) returns the number of characters in string, "Anand" has 5 letters, so 5. Straightforward, and the kind of thing that comes up constantly alongside LEFT, SUBSTR, and INSTR, whenever a function needs to know how long a string actually is before slicing into it.

RIGHT()

LEFT, from the very first section, pulls from the front:

select left("Anup Kumar", 4);

+-------------------------+
| left("Anup Kumar", 4)   |
+-------------------------+
| Anup                    |
+-------------------------+
1 row in set (0.00 sec)

RIGHT is its mirror, pulling from the back instead:

select right("Anup Kumar", 5);

+--------------------------+
| right("Anup Kumar", 5)   |
+--------------------------+
| Kumar                    |
+--------------------------+
1 row in set (0.00 sec)

RIGHT(string, n) returns the last n characters, counting from the right this time. "Anup Kumar" is 10 characters including the space, its last 5 are "Kumar". Same signature as LEFT, same idea, just anchored to the opposite end of the string.

MID()

select mid("Anup Kumar Singh",6,5);

+-------------------------------+
| mid("Anup Kumar Singh", 6, 5) |
+-------------------------------+
| Kumar                         |
+-------------------------------+
1 row in set (0.00 sec)

MID(string, start, length) is another name for SUBSTR, same three arguments, same behavior, same 1-indexed start. "Anup Kumar Singh", starting at position 6 ('K') and taking 5 characters, gives "Kumar". Whichever of MID or SUBSTR shows up in a query, it's doing the exact same thing.

CURDATE() / CURRENT_DATE()

select curdate();

+------------+
| curdate()  |
+------------+
| 2026-08-30 |
+------------+
1 row in set (0.01 sec)

select current_date();

+----------------+
| current_date() |
+----------------+
| 2026-08-30     |
+----------------+
1 row in set (0.00 sec)

CURDATE() returns today's date, no arguments needed. CURRENT_DATE() is the exact same function under a longer, more descriptive name, same pairing pattern as POWER/POW earlier, both spellings return today's date identically.

DATE_ADD() with INTERVAL

select date_add(curdate(), interval 10 day);

+---------------------------------+
| date_add(curdate(), interval 10 day) |
+---------------------------------+
| 2026-09-09                       |
+---------------------------------+
1 row in set (0.00 sec)

DATE_ADD(date, INTERVAL n unit) adds n units of time to date. INTERVAL 10 DAY means "10 days", added on top of CURDATE(), today's date shifted 10 days forward. The unit isn't limited to DAY, MONTH, YEAR, HOUR, and others all work the same way, INTERVAL n unit reads almost like plain English once it clicks.

DATE()

select date(doj) from employee;

+------------+
| date(doj)  |
+------------+
| 2020-12-23 |
| 2023-04-12 |
| 2026-04-01 |
| 2019-07-19 |
| 2022-09-30 |
| 2022-10-30 |
| 2020-12-23 |
| 2020-12-23 |
+------------+
8 rows in set (0.01 sec)

DATE(expression) extracts just the date portion out of a value. doj is already a plain DATE column here, so it looks like it does nothing, but this function earns its keep on a DATETIME or TIMESTAMP column, one that also carries a time component, where DATE(...) strips that time part off and leaves only YYYY-MM-DD.

MONTH() / MONTHNAME()

select month(doj) from employee;

+------------+
| month(doj) |
+------------+
|         12 |
|          4 |
|          4 |
|          7 |
|          9 |
|         10 |
|         12 |
|         12 |
+------------+
8 rows in set (0.00 sec)

MONTH(date) returns the month as a plain number, 1 through 12. For the month's name instead, the function is MONTHNAME, one word, no underscore, month_name isn't valid and errors out:

select monthname(doj) from employee;

+----------------+
| monthname(doj) |
+----------------+
| December       |
| April          |
| April          |
| July           |
| September      |
| October        |
| December       |
| December       |
+----------------+
8 rows in set (0.01 sec)

MONTHNAME(date) returns the full month name, 12 becomes "December", spelled out instead of numeric.

DAY() / DAYNAME()

select day(doj) from employee;

+-----------+
| day(doj)  |
+-----------+
|        23 |
|        12 |
|         1 |
|        19 |
|        30 |
|        30 |
|        23 |
|        23 |
+-----------+
8 rows in set (0.00 sec)

DAY(date) returns the day-of-month as a number, 1 through 31. Works the same on any date expression, not just a column, DAY(CURDATE()) returns today's day-of-month:

select day(curdate());

+-----------------+
| day(curdate())  |
+-----------------+
|               30 |
+-----------------+
1 row in set (0.00 sec)

For the weekday's name instead of a day-of-month number, DAYNAME:

select dayname(curdate());

+---------------------+
| dayname(curdate())  |
+---------------------+
| Sunday               |
+---------------------+
1 row in set (0.00 sec)

DAYNAME(date) returns which day of the week date falls on, "Sunday", "Monday", and so on, spelled out the same way MONTHNAME spells out months.

YEAR, from earlier, works the same way on any date expression too, not just a table column:

select year(curdate());

+------------------+
| year(curdate())  |
+------------------+
|              2026 |
+------------------+
1 row in set (0.00 sec)

DAYOFMONTH() / DAYOFWEEK() / DAYOFYEAR()

Three siblings, each answering a slightly different "which day" question, all as a plain number.

select dayofmonth(curdate());

+-------------------------+
| dayofmonth(curdate())   |
+-------------------------+
|                       30 |
+-------------------------+
1 row in set (0.00 sec)

DAYOFMONTH(date) is the exact same thing as DAY(date) from above, two names for the identical function, 30.

select dayofweek(curdate());

+------------------------+
| dayofweek(curdate())   |
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

DAYOFWEEK(date) returns a number 1–7 for the day of the week, 1 meaning Sunday, matching DAYNAME returning "Sunday" for the same date, just numeric instead of spelled out.

select dayofyear(curdate());

+------------------------+
| dayofyear(curdate())   |
+------------------------+
|                    242 |
+------------------------+
1 row in set (0.00 sec)

DAYOFYEAR(date) counts how many days into the year date is, 242 meaning today is the 242nd day of 2026.

NOW() / SYSDATE()

select now();

+---------------------+
| now()               |
+---------------------+
| 2026-08-30 20:09:25 |
+---------------------+
1 row in set (0.00 sec)

select sysdate();

+---------------------+
| sysdate()           |
+---------------------+
| 2026-08-30 20:09:49 |
+---------------------+
1 row in set (0.00 sec)

Both return the current date and time together, unlike CURDATE() which only returns the date. The subtle difference between them: NOW() is fixed at the moment the statement started running, every call to NOW() within the same statement returns the same value, while SYSDATE() reflects the exact moment it's actually evaluated, which is why the two timestamps above are 24 seconds apart despite being run moments apart, sysdate() was simply typed and run a bit later.