Single-Row SQL Functions: Math, String & Date

A Single-Row Function (or Scalar Function) operates on a single value per row and returns a single modified value for every row processed. In CBSE Class 12 IP (Code 065), single-row functions are divided into three categories: Mathematical Functions, String (Text) Functions, and Date/Time Functions.

---

1. Mathematical Functions

1.1 POWER(X, Y) or POW(X, Y)

Returns the value of $X$ raised to the power of $Y$ ($X^Y$).

SELECT POWER(2, 3);   -- Output: 8
SELECT POWER(5, -2);  -- Output: 0.04 (1/25)
SELECT POWER(9, 0.5); -- Output: 3

---

1.2 ROUND(N, [D])

Rounds the number $N$ to $D$ decimal places. If $D$ is omitted, it defaults to 0.

IMPORTANT

CBSE High-Yield Concept: Negative values of $D$ round to the left of the decimal point (tens, hundreds, etc.).

$$\text{If }D>0⟹\text{Round to }D\text{ decimal places to the right}$$$$\text{If }D=0⟹\text{Round to the nearest integer}$$$$\text{If }D<0⟹\text{Round to the }|D|\text{-th digit to the left (tens for -1, hundreds for -2)}$$

SELECT ROUND(145.678, 2);   -- Output: 145.68
SELECT ROUND(145.678, 0);   -- Output: 146
SELECT ROUND(145.678);      -- Output: 146
SELECT ROUND(145.678, -1);  -- Output: 150 (rounds to nearest 10)
SELECT ROUND(145.678, -2);  -- Output: 100 (rounds to nearest 100)
SELECT ROUND(678.92, -3);   -- Output: 1000

---

1.3 TRUNCATE(N, D)

Truncates the number $N$ to $D$ decimal places without rounding.

SELECT TRUNCATE(145.678, 2);  -- Output: 145.67 (simply drops digits)
SELECT TRUNCATE(145.678, 0);  -- Output: 145
SELECT TRUNCATE(145.678, -1); -- Output: 140

---

1.4 MOD(N, M)

Returns the remainder of $N$ divided by $M$ ($N(\mathrm{mod}M)$). Equivalent to $N-M\times \text{FLOOR}(N/M)$ or $N\mathrm{\%}M$.

SELECT MOD(11, 3);    -- Output: 2
SELECT MOD(11.5, 3);  -- Output: 2.5
SELECT MOD(15, 0);    -- Output: NULL (division by zero)

---

2. String (Text) Functions

NOTE

In MySQL SQL, string indexing is 1-based (the first character is at index 1, NOT 0).

2.1 UCASE(str) / UPPER(str) & LCASE(str) / LOWER(str)

Converts all characters in the string to uppercase or lowercase.

SELECT UCASE('Informatics Practices'); -- Output: 'INFORMATICS PRACTICES'
SELECT LCASE('CBSE Class 12');         -- Output: 'cbse class 12'

---

2.2 MID(str, pos, len) / SUBSTRING(str, pos, len) / SUBSTR(str, pos, len)

Extracts a substring from str starting at position pos for a length of len characters. If len is omitted, extracts all characters from pos to the end of the string.

WARNING

Negative pos starts counting backwards from the end of the string (where -1 is the last character).

SELECT MID('INFORMATICS', 3, 4);   -- Output: 'FORM' (starts at 3, takes 4 chars)
SELECT SUBSTR('INFORMATICS', 6);    -- Output: 'MATICS' (starts at 6 to end)
SELECT SUBSTRING('INFORMATICS', -4, 3); -- Output: 'TIC' (starts 4th from right: 'T', takes 3)

---

2.3 LENGTH(str)

Returns the length of the string in bytes/characters.

SELECT LENGTH('CBSE 2026'); -- Output: 9 (spaces are counted!)
SELECT LENGTH('');          -- Output: 0
SELECT LENGTH(NULL);        -- Output: NULL

---

2.4 LEFT(str, len) & RIGHT(str, len)

Extracts len characters from the start (left) or end (right) of the string.

SELECT LEFT('FLUGEL', 3);  -- Output: 'FLU'
SELECT RIGHT('FLUGEL', 3); -- Output: 'GEL'

---

2.5 INSTR(str, substr)

Returns the position of the first occurrence of substr in str. If substr is not found, returns 0.

SELECT INSTR('INFORMATICS', 'FOR');  -- Output: 3
SELECT INSTR('INFORMATICS', 'MA');   -- Output: 6
SELECT INSTR('INFORMATICS', 'JAVA'); -- Output: 0

---

2.6 LTRIM(str), RTRIM(str) & TRIM(str)

Removes leading (left), trailing (right), or both spaces from str.

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

---

3. Date and Time Functions

MySQL stores dates in the standard ISO format: 'YYYY-MM-DD' and datetime as 'YYYY-MM-DD HH:MM:SS'.

3.1 NOW() & SYSDATE()

Returns the current date and time.

SELECT NOW();     -- Example Output: '2026-09-19 10:30:45'
SELECT SYSDATE(); -- Example Output: '2026-09-19 10:30:45'

---

3.2 CURDATE() / CURRENT_DATE() & CURTIME() / CURRENT_TIME()

Returns the current date only or current time only.

SELECT CURDATE(); -- Example Output: '2026-09-19'
SELECT CURTIME(); -- Example Output: '10:30:45'

---

3.3 Component Extractors: DATE(), MONTH(), YEAR(), DAY(), DAYOFMONTH()

SELECT DATE('2026-09-19 14:20:00'); -- Output: '2026-09-19'
SELECT YEAR('2026-09-19');          -- Output: 2026
SELECT MONTH('2026-09-19');         -- Output: 9
SELECT DAY('2026-09-19');           -- Output: 19
SELECT DAYOFMONTH('2026-09-19');    -- Output: 19

---

3.4 Name Extractors: MONTHNAME(date) & DAYNAME(date)

Returns the full English name of the month or weekday.

SELECT MONTHNAME('2026-09-19'); -- Output: 'September'
SELECT DAYNAME('2026-09-19');   -- Output: 'Saturday'

---

3.5 Day of Week & Day of Year: DAYOFWEEK(date) & DAYOFYEAR(date)

NOTE

DAYOFWEEK() returns an integer from 1 (Sunday) to 7 (Saturday) as per ODBC standard.

SELECT DAYOFWEEK('2026-09-19'); -- Output: 7 (Saturday)
SELECT DAYOFYEAR('2026-09-19'); -- Output: 262

---

4. Comprehensive Function Quick-Lookup Table

Function Category	Function Name	Syntax	Example Output
Math	POWER(X, Y)	SELECT POWER(3, 3);	27
Math	ROUND(N, D)	SELECT ROUND(78.567, 1);	78.6
Math	MOD(N, M)	SELECT MOD(17, 5);	2
String	UCASE(str)	SELECT UCASE('cbse');	'CBSE'
String	LCASE(str)	SELECT LCASE('IP');	'ip'
String	MID(str, p, l)	SELECT MID('PYTHON', 2, 3);	'YTH'
String	LENGTH(str)	SELECT LENGTH('Board 2026');	10
String	LEFT(str, n)	SELECT LEFT('DATABASE', 4);	'DATA'
String	RIGHT(str, n)	SELECT RIGHT('DATABASE', 4);	'BASE'
String	INSTR(str, sub)	SELECT INSTR('PANDAS', 'DA');	4
String	TRIM(str)	SELECT TRIM(' SQL ');	'SQL'
Date	NOW()	SELECT NOW();	Current Datetime
Date	DATE(dt)	SELECT DATE(NOW());	Current Date
Date	MONTH(d)	SELECT MONTH('2026-12-25');	12
Date	MONTHNAME(d)	SELECT MONTHNAME('2026-12-25');	'December'
Date	YEAR(d)	SELECT YEAR('2026-12-25');	2026
Date	DAY(d)	SELECT DAY('2026-12-25');	25
Date	DAYNAME(d)	SELECT DAYNAME('2026-12-25');	'Friday'
Date	DAYOFWEEK(d)	SELECT DAYOFWEEK('2026-12-25');	6 (Friday)

---

5. Board Exam Output Questions

Question 1 (CBSE 2024 Past Paper)

Predict the output of the following SQL queries:

1. SELECT ROUND(234.56, -1);
2. SELECT MID('Artificial Intelligence', 12, 5);
3. SELECT INSTR('Database Management', 'base');
4. SELECT DAYNAME('2026-01-01'); (Given Jan 1, 2026 is Thursday)

Solutions:

1. 230 (Rounds 234 to nearest 10)
2. 'Intel' (Starts at index 12: 'I', takes 5 characters)
3. 5 (First occurrence of 'base' begins at character 5)
4. 'Thursday'