SQL Functions – Transforming and Analyzing Data with Built-in Power

SQL functions are predefined operations that perform transformations or computations on data. These functions range from simple aggregations like SUM() and COUNT() to powerful string, date, and mathematical operations. Understanding and using SQL functions effectively allows you to write concise, efficient, and expressive queries.

In this chapter, we’ll explore the wide range of SQL functions, categorized by their purpose:

Aggregate Functions
Scalar Functions (String, Numeric, and Date/Time)
Conversion Functions
Conditional Functions
User-Defined Functions (UDFs)

We’ll cover syntax, use cases, examples, best practices, and system-specific notes (MySQL, PostgreSQL, SQL Server, Oracle).

Aggregate Functions

Aggregate functions operate on a set of rows and return a single result. They are commonly used with GROUP BY to summarize data.

COUNT()

Counts the number of rows or non-null values.

SELECT COUNT(*) FROM Employees;
SELECT COUNT(Email) FROM Employees WHERE Email IS NOT NULL;

SUM()

Adds values in a numeric column.

SELECT SUM(Salary) AS TotalSalaries FROM Employees;

AVG()

Calculates the average of a numeric column.

SELECT AVG(Age) FROM Customers;

MIN() and MAX()

Returns the smallest/largest value.

SELECT MIN(HireDate), MAX(HireDate) FROM Employees;

GROUP BY Usage

SELECT Department, COUNT(*) AS Headcount
FROM Employees
GROUP BY Department;

Note: Aggregate functions ignore NULL values unless COUNT(*) is used.

String Functions

String functions operate on character data (CHAR, VARCHAR, TEXT).

LENGTH(), CHAR_LENGTH(), LEN()

Returns length of a string.

SELECT LENGTH('SQL Tutorial');

CONCAT(), CONCAT_WS()

Concatenates strings with or without a separator.

SELECT CONCAT(FirstName, ' ', LastName) AS FullName FROM Users;
SELECT CONCAT_WS('-', AreaCode, Number) FROM Phones;

UPPER(), LOWER()

Converts strings to uppercase or lowercase.

SELECT UPPER(Name), LOWER(Name) FROM Products;

SUBSTRING(), LEFT(), RIGHT()

Extracts part of a string.

SELECT SUBSTRING(Name, 1, 5) FROM Employees;
SELECT LEFT(Email, 5) FROM Customers;

TRIM(), LTRIM(), RTRIM()

Removes spaces or characters.

SELECT TRIM(' SQL ') AS Cleaned;

REPLACE()

Replaces parts of strings.

SELECT REPLACE(Email, '@example.com', '@newdomain.com');

POSITION(), INSTR(), CHARINDEX()

Finds position of a substring.

SELECT POSITION('e' IN 'Welcome');

Date and Time Functions

These functions work with DATE, TIME, DATETIME, and TIMESTAMP data types.

CURRENT_DATE, NOW(), GETDATE()

Returns the current date and time.

SELECT CURRENT_DATE;
SELECT NOW(); -- MySQL/PostgreSQL
SELECT GETDATE(); -- SQL Server

DATE_PART(), EXTRACT()

Extracts part of a date (year, month, day).

SELECT EXTRACT(YEAR FROM OrderDate) FROM Orders;

DATE_ADD(), DATE_SUB()

Adds or subtracts intervals.

SELECT DATE_ADD(NOW(), INTERVAL 7 DAY);
SELECT DATE_SUB(NOW(), INTERVAL 1 MONTH);

DATEDIFF(), AGE()

Finds difference between dates.

SELECT DATEDIFF('2025-01-01', '2024-01-01'); -- MySQL
SELECT AGE('2025-01-01', '2024-01-01'); -- PostgreSQL

TO_CHAR(), FORMAT()

Formats date/time output.

SELECT TO_CHAR(OrderDate, 'YYYY-MM-DD') FROM Orders;

Numeric and Mathematical Functions

Used for mathematical operations, rounding, and number handling.

ABS()

Returns absolute value.

SELECT ABS(-42);

ROUND(), CEIL(), FLOOR()

Rounding functions.

SELECT ROUND(123.456, 2);
SELECT CEIL(4.2); -- returns 5
SELECT FLOOR(4.8); -- returns 4

POWER(), SQRT(), EXP(), LOG()

Exponents, square roots, logarithms.

SELECT POWER(2, 3); -- 8
SELECT SQRT(25); -- 5

MOD(), %

Modulus (remainder).

SELECT MOD(10, 3); -- 1

Conversion Functions

Convert between data types.

CAST()

SELECT CAST('123' AS INT);

CONVERT()

SELECT CONVERT(INT, '456'); -- SQL Server

TO_DATE(), TO_NUMBER(), TO_CHAR()

SELECT TO_DATE('2024-01-01', 'YYYY-MM-DD');
SELECT TO_NUMBER('12345', '99999');

These are useful for formatting, parsing, and ensuring type safety.

Conditional Functions

CASE

Provides if-else logic.

SELECT Name,
  CASE
    WHEN Salary > 100000 THEN 'High'
    WHEN Salary > 50000 THEN 'Medium'
    ELSE 'Low'
  END AS SalaryBand
FROM Employees;

COALESCE()

Returns the first non-null value.

SELECT COALESCE(MiddleName, 'N/A') FROM Users;

NULLIF()

Returns NULL if two expressions are equal.

SELECT NULLIF(Score1, Score2);

JSON Functions (in supported databases)

PostgreSQL:

SELECT data->>'name' FROM users WHERE data->>'status' = 'active';

MySQL:

SELECT JSON_EXTRACT(json_col, '$.user.name') FROM users;

User-Defined Functions (UDFs)

Some RDBMS allow you to create custom functions using SQL or procedural languages.

Example – PostgreSQL:

CREATE FUNCTION get_discount(price DECIMAL)
RETURNS DECIMAL AS $$
BEGIN
  RETURN price * 0.90;
END;
$$ LANGUAGE plpgsql;

SELECT get_discount(100);

Benefits:

Encapsulate business logic
Reusable and testable
Improve code readability

Real-World Use Case: Reporting Dashboard

Suppose you are building a sales dashboard for a retail system.

Top 5 Products by Revenue:

SELECT ProductID, SUM(Amount) AS TotalRevenue
FROM Sales
GROUP BY ProductID
ORDER BY TotalRevenue DESC
LIMIT 5;

Monthly Sales Summary:

SELECT EXTRACT(MONTH FROM SaleDate) AS Month,
       SUM(Amount) AS TotalSales
FROM Sales
GROUP BY EXTRACT(MONTH FROM SaleDate)
ORDER BY Month;

Classify Orders by Amount:

SELECT OrderID,
  CASE
    WHEN Amount >= 1000 THEN 'High'
    WHEN Amount >= 500 THEN 'Medium'
    ELSE 'Low'
  END AS OrderSize
FROM Orders;

Format Customer Contact Info:

SELECT CONCAT(Name, ' - ', COALESCE(Phone, 'No Phone')) AS Contact
FROM Customers;

Best Practices for Using Functions

Avoid overusing functions in WHERE clauses – they can prevent index usage.
Use built-in functions instead of reinventing logic.
Apply formatting at presentation layer unless using TO_CHAR for reporting.
Validate function compatibility – syntax varies across RDBMS.
Use COALESCE over ISNULL or NVL for portability.
**Limit function usage in SELECT *** if not required – improve performance.

Common Mistakes to Avoid

Mistake	Impact
Using functions in WHERE on indexed column	Breaks index usage – slows queries
Using incompatible types in CONCAT/CAST	Runtime errors or data truncation
Forgetting NULL behavior in aggregates	Misleading averages or totals
Applying ROUND() too early	Loss of precision in calculations
Assuming function support across DBMSs	Portability issues

Summary

SQL functions are powerful tools that allow you to:

Perform calculations, transformations, and formatting
Analyze and summarize datasets
Implement business logic using CASE and COALESCE
Enhance reporting and dashboarding

By mastering built-in and user-defined functions, you’ll significantly boost your ability to write flexible, reusable, and readable SQL code.

In the next chapter, we’ll explore Views, Indexes, and Stored Procedures — essential features for encapsulating logic, improving performance, and managing database complexity.

Table of Contents