Math and text functions

The essentials

Single-row functions act on one value per row and return one value per row. MySQL provides math functions and text (string) functions. In MySQL, string positions start at 1, not 0.

Key points

• POWER(x, y) or POW(x, y): x raised to y. POWER(2, 3) gives 8.
• ROUND(n, d): rounds n to d decimal places. ROUND(25.678, 1) gives 25.7. With d negative, it rounds to the left of the decimal point: ROUND(1456, -2) gives 1500. ROUND(n) with no d rounds to a whole number.
• MOD(a, b): remainder of a divided by b. MOD(17, 5) gives 2.
• UPPER(s) / UCASE(s) and LOWER(s) / LCASE(s): change case.
• LENGTH(s): number of characters (bytes) in s. LENGTH('India') gives 5; spaces count.
• SUBSTRING(s, pos, n) (also SUBSTR or MID): n characters starting at position pos. SUBSTRING('COMPUTER', 4, 3) gives 'PUT'.
• LEFT(s, n) and RIGHT(s, n): first or last n characters. LEFT('SCIENCE', 3) gives 'SCI'; RIGHT('SCIENCE', 3) gives 'NCE'.
• INSTR(s, sub): position of the first occurrence of sub in s; 0 if not found. INSTR('BANANA', 'NA') gives 3.
• LTRIM, RTRIM, TRIM: remove leading, trailing, or both leading and trailing spaces. TRIM does not remove spaces in the middle.

Exam tips

• Count positions carefully from 1. Write the string with numbers under each letter.
• Functions can be nested: UPPER(LEFT('delhi', 3)) gives 'DEL'.
• Functions can also be used on columns: SELECT ROUND(Salary * 0.1, 2) FROM Emp;

Common mistakes

• Counting positions from 0, as in Python.
• Thinking TRIM(' a b ') returns 'ab'. It returns 'a b'.