What are single-row functions in SQL?

Functions that work on one row at a time and return one value per row, such as ROUND, UPPER and MONTHNAME.

What is the difference between SUBSTR and INSTR in MySQL?

SUBSTR(s, start, n) returns n characters from position start; INSTR(s, sub) returns the position where sub first appears, or 0.

What does ROUND with a negative number do?

It rounds to the left of the decimal point: -1 to the nearest ten, -2 to the nearest hundred.

SQL Functions Class 12 IP: Math, Text and Date

A single-row function takes one value from a row and returns one value, so a column of n rows gives n answers. Maths functions: POWER, ROUND and MOD. Text (string) functions: UPPER, LOWER, LENGTH, LEFT, RIGHT, SUBSTR, INSTR and TRIM. Date functions: NOW, DATE, MONTH, MONTHNAME, YEAR, DAY and DAYNAME. They are used inside SELECT, WHERE and ORDER BY.

What is a single-row function?

A function is a ready-made command that takes values (called arguments) and returns a result. A single-row (scalar) function works on one row at a time and gives one result per row. So if a table has 10 rows, SELECT UPPER(Name) FROM student; gives 10 answers.

(Aggregate functions like SUM work on many rows and give one answer; they are in the next lesson.) You can try any function without a table: SELECT POWER(2, 3);

Maths functions: POWER, ROUND, MOD

Function	Meaning	Example → output
`POWER(x, y)` or `POW`	x raised to y	POWER(2, 3) → 8
`ROUND(x, d)`	round x to d decimal places	ROUND(15.678, 2) → 15.68
`ROUND(x)`	round to a whole number	ROUND(15.678) → 16
`ROUND(x, -1)`	round to nearest 10	ROUND(15.678, -1) → 20
`MOD(a, b)`	remainder of a ÷ b	MOD(17, 5) → 2

ROUND looks at the next digit: 5 or more rounds up. A negative d rounds to the left of the decimal point (-1 → tens, -2 → hundreds).

Text functions: UPPER, LOWER, LENGTH, LEFT, RIGHT, SUBSTR, INSTR, TRIM

Function	Meaning	Example → output
`UPPER(s)` / `UCASE`	capital letters	UPPER('asha') → ASHA
`LOWER(s)` / `LCASE`	small letters	LOWER('INDIA') → india
`LENGTH(s)`	number of characters (spaces count)	LENGTH('Informatics') → 11
`LEFT(s, n)`	first n characters	LEFT('Informatics', 4) → Info
`RIGHT(s, n)`	last n characters	RIGHT('Informatics', 3) → ics
`SUBSTR(s, start, n)` / `MID`	n characters from position start	SUBSTR('Informatics', 3, 4) → form
`INSTR(s, sub)`	position where sub first starts (0 if not found)	INSTR('Informatics', 'for') → 3
`TRIM(s)`	remove spaces at both ends	TRIM(' IP ') → 'IP'
`LTRIM(s)` / `RTRIM(s)`	remove spaces on left / right only	LTRIM(' IP ') → 'IP '

Positions start at 1. SUBSTR without n takes everything to the end: SUBSTR('Informatics', 6) → 'matics'. A negative start counts from the end: SUBSTR('Informatics', -3) → 'ics'.

Date functions: NOW, DATE, MONTH, MONTHNAME, YEAR, DAY, DAYNAME

MySQL writes dates as 'YYYY-MM-DD'.

Function	Example → output
`NOW()`	current date and time, e.g. 2026-09-30 10:15:00
`DATE(dt)`	DATE('2026-09-30 10:15:00') → 2026-09-30
`MONTH(d)`	MONTH('2026-09-30') → 9
`MONTHNAME(d)`	MONTHNAME('2026-09-30') → September
`YEAR(d)`	YEAR('2026-09-30') → 2026
`DAY(d)`	DAY('2026-09-30') → 30
`DAYNAME(d)`	DAYNAME('2026-09-30') → Wednesday

NOW() changes every time you run it, so board questions usually give a fixed date.

Using functions on a table

SELECT Name, UPPER(Name), LENGTH(Name) FROM student;
SELECT Name, ROUND(Fee * 1.18, 2) AS FeeWithGST FROM student;
SELECT * FROM student WHERE MONTHNAME(DOB) = 'August';
SELECT * FROM student WHERE MOD(Roll, 2) = 0;      -- even roll numbers
SELECT Name FROM student ORDER BY LENGTH(Name);

AS gives the output column a nicer name (an alias).

Try it: your name and birthday

On paper, write your full name and date of birth. Predict: LENGTH(name), LEFT(name, 3), INSTR(name, 'a'), DAYNAME(dob), MONTHNAME(dob). Then run each in MySQL or an online SQL playground, e.g. SELECT DAYNAME('2009-08-15');. Count how many predictions were right. Then test more in step 6 of the 3D.

Key formulas and definitions

POWER(x, y) · ROUND(x, d) · MOD(a, b)
UPPER/LOWER · LENGTH · LEFT(s, n) · RIGHT(s, n) · SUBSTR(s, start, n) · INSTR(s, sub) · TRIM/LTRIM/RTRIM
NOW() · DATE() · MONTH() · MONTHNAME() · YEAR() · DAY() · DAYNAME()
Positions in SQL text start at 1 · INSTR gives 0 when not found

Worked examples

1. Find the output: SELECT POWER(5, 2), MOD(47, 10), ROUND(7.456, 1);

25, 7, 7.5.

2. Find the output: SELECT ROUND(2468.5), ROUND(2468.5, -2), ROUND(2468.537, 2);

2469 (0.5 rounds up), 2500 (nearest hundred), 2468.54.

3. Find the output: SELECT SUBSTR('CodingMarble', 7, 6), INSTR('CodingMarble', 'Mar'), LENGTH('CodingMarble');

'Marble' (starts at position 7, takes 6), 7, 12.

4. Find the output: SELECT LEFT('Informatics Practices', 11), RIGHT('Informatics Practices', 9), UPPER(LEFT('practices', 1));

'Informatics', 'Practices', 'P'.

5. For the date '2026-01-26', find MONTH, MONTHNAME, DAY, YEAR and DAYNAME.

1, January, 26, 2026, Monday.

6. Write a query to show names of students whose name has 5 or more letters, in capital letters.

SELECT UPPER(Name) FROM student WHERE LENGTH(Name) >= 5;

7. What is the output of SELECT LENGTH(TRIM(' Hi IP '));?

TRIM gives 'Hi IP' (inside space stays). LENGTH = 5.

Common mistakes

Counting text positions from 0; SQL starts at 1, so SUBSTR('Hello', 1, 2) is 'He'.
Mixing up SUBSTR (returns a piece of text) and INSTR (returns a number, the position).
Thinking TRIM removes spaces inside the text; it removes only the leading and trailing spaces.
Writing ROUND(15.678, 1) as 15.6; the next digit 7 rounds it up to 15.7.

Practice quiz

Practice: answer these yourself

Type or choose your answer, then press Check. Use a hint if you are stuck; the full solution appears after you answer.

SQL Single-Row Functions: Maths, Text and Date

🎬 Step-by-step story

🤔 Common doubts, cleared

What does 'single-row' mean?

Why is ROUND(15.678, -1) equal to 20?

Does SQL count the first letter as 0 or 1?

What is the difference between SUBSTR and INSTR?

Why does INSTR sometimes give 0?

What is the difference between DAY() and DAYNAME()?

Why did I get 5 answers from one function?