What Are SQL Functions?

Your IPL table stores names like Virat Kohli, runs like 750 and dates like 2008-04-20. But reports rarely want the raw value — you want the name in capitals, the strike rate to two decimals, the debut year only, or "Unsold" instead of an empty team. Functions are the built-in tools that do this work inside the query itself, without changing the stored data.

Definition

A SQL function is a named, built-in operation that takes one or more input values (arguments), performs a calculation or transformation, and returns an output value. Functions can be used wherever a value is allowed — in SELECT, WHERE, ORDER BY, GROUP BY and more.

• Named — every function has a fixed name such as LOWER, ROUND or YEAR, followed by brackets.
• Arguments — the values you put inside the brackets. Some functions take one (UPPER(name)), some take several (ROUND(x, 2)), and some take none (GETDATE()).
• Returns a value — a function always hands back a result that you can display, compare, sort by, or pass into another function.
• Functions do not modify the table. UPPER(name) only changes what you see in the result; the stored name stays exactly as it was.

Single-row vs multi-row functions

Single-row functions run once for every row, independently of the other rows. Multi-row functions read a whole set of rows and squeeze them into a single answer.

This chapter is all about single-row functions (also called row-level functions or scalar functions). Aggregate and window functions come in the next chapter.

Syntax

FUNCTION_NAME(argument1, argument2, ...)

Nested functions

A nested function is a function used as the argument of another function. SQL always evaluates the innermost function first and passes its result outwards.

SELECT LEN(LOWER(LEFT('VIRAT', 2))) AS result;

Read nested functions from the inside out, like peeling an onion. There is no fixed limit you will realistically hit — but more than 2–3 levels gets hard to read, so format long ones over several lines.

The SQL functions family

The CASE expression is not a function by name, but it behaves like one (one value in, one value out per row), so it is covered in this chapter too.

Example — the nested-function steps, one column per step

SELECT
    LEFT('VIRAT', 2)               AS step1,
    LOWER(LEFT('VIRAT', 2))        AS step2,
    LEN(LOWER(LEFT('VIRAT', 2)))   AS result;

How it works: each column adds one more layer around the previous one, so you can see the inside-out evaluation.

Common mistakes

• ❌ SELECT GETDATE; → ✅ SELECT GETDATE(); — brackets are mandatory even with no arguments.
• ❌ Expecting UPDATE-like behaviour from UPPER(name) → ✅ functions in SELECT only change the output, never the stored data.
• ❌ Mixing up row-level and aggregate functions: SELECT name, SUM(runs) FROM players fails in SQL Server because SUM collapses rows but name does not.

Interview tip

Q: What is the difference between a scalar (single-row) function and an aggregate function? A scalar function returns one value for each row (UPPER(name) keeps 6 rows as 6 rows). An aggregate function returns one value for a group of rows (SUM(runs) turns 6 rows into 1). Scalar functions can be used freely in WHERE; aggregate functions cannot be used in WHERE — they belong in SELECT/HAVING.