SQL Functions Complete Guide: String, Date, Math, Aggregate Functions Summary [2025]

SQL built-in functions are powerful tools for database development, allowing you to process strings, dates, numbers, and various data types directly within queries. Mastering these functions can greatly simplify program logic, completing calculations directly in the database instead of in the application layer, improving overall performance.

This article systematically introduces the most commonly used SQL built-in functions, including string functions, date functions, math functions, conversion functions, and aggregate functions. Each function includes syntax explanation and practical examples, with a quick reference table at the end.

---

SQL Functions Category Overview

SQL functions can be categorized by the data type they process:

Function Type	Purpose	Common Functions
String Functions	Process text data	CONCAT, SUBSTRING, REPLACE, TRIM
Date Functions	Process date/time	GETDATE, DATEADD, DATEDIFF, FORMAT
Math Functions	Numeric operations	ROUND, CEILING, FLOOR, ABS
Conversion Functions	Data type conversion	CAST, CONVERT, COALESCE
Aggregate Functions	Multi-row summary calculations	COUNT, SUM, AVG, MAX, MIN

Note: Different databases (SQL Server, MySQL, PostgreSQL) may have slightly different function syntax. This article primarily uses SQL Server syntax, noting important differences where applicable.

---

String Functions

String functions are used for text data processing, including concatenation, extraction, replacement, and trimming whitespace.

CONCAT - String Concatenation

Combine multiple strings into one string.

-- Syntax
CONCAT(string1, string2, ...)

-- Example: Combine names
SELECT CONCAT(FirstName, ' ', LastName) AS FullName
FROM Employees;
-- Result: Alice Chen

-- Example: Combine address
SELECT CONCAT(City, ', ', Country) AS Location
FROM Customers;
-- Result: Taipei, Taiwan

Database Differences:

Database	Syntax
SQL Server	CONCAT(a, b) or a + b
MySQL	CONCAT(a, b)
PostgreSQL	CONCAT(a, b) or `a

LEN / LENGTH - String Length

Get the character count of a string.

-- SQL Server uses LEN
SELECT LEN('Hello World') AS StringLength;
-- Result: 11

-- MySQL / PostgreSQL use LENGTH
SELECT LENGTH('Hello World') AS StringLength;
-- Result: 11

-- Practical example: Find products with names too long
SELECT ProductName, LEN(ProductName) AS NameLength
FROM Products
WHERE LEN(ProductName) > 50;

SUBSTRING / SUBSTR - Extract Substring

Extract a substring from a specific position and length.

-- Syntax
SUBSTRING(string, start_position, length)

-- Example: Extract first 3 characters
SELECT SUBSTRING('Hello World', 1, 3) AS Result;
-- Result: Hel

-- Example: Extract area code from ID number
SELECT SUBSTRING(IDNumber, 1, 1) AS AreaCode
FROM Citizens;

-- Example: Extract username from Email (before @)
SELECT SUBSTRING(Email, 1, CHARINDEX('@', Email) - 1) AS Username
FROM Users;

REPLACE - String Replacement

Replace specified text with other text in a string.

-- Syntax
REPLACE(original_string, text_to_replace, replacement_text)

-- Example: Replace space with underscore
SELECT REPLACE('Hello World', ' ', '_') AS Result;
-- Result: Hello_World

-- Example: Remove dashes from phone numbers
SELECT REPLACE(PhoneNumber, '-', '') AS CleanPhone
FROM Customers;
-- Original: 02-1234-5678 → Result: 0212345678

-- Example: Mask sensitive data
SELECT REPLACE(CreditCard, SUBSTRING(CreditCard, 5, 8), '****-****') AS MaskedCard
FROM Payments;

TRIM / LTRIM / RTRIM - Remove Whitespace

Remove whitespace characters from the beginning and/or end of a string.

-- TRIM: Remove leading and trailing whitespace
SELECT TRIM('  Hello World  ') AS Result;
-- Result: Hello World

-- LTRIM: Remove left whitespace only
SELECT LTRIM('  Hello') AS Result;
-- Result: Hello

-- RTRIM: Remove right whitespace only
SELECT RTRIM('Hello  ') AS Result;
-- Result: Hello

-- Practical example: Clean imported data
UPDATE Customers
SET CustomerName = TRIM(CustomerName),
    Email = LOWER(TRIM(Email));

UPPER / LOWER - Case Conversion

-- Convert to uppercase
SELECT UPPER('hello') AS Result;  -- HELLO

-- Convert to lowercase
SELECT LOWER('HELLO') AS Result;  -- hello

-- Practical example: Normalize Email (all lowercase)
SELECT LOWER(Email) AS NormalizedEmail FROM Users;

-- Practical example: Capitalize first letter (SQL Server)
SELECT UPPER(LEFT(Name, 1)) + LOWER(SUBSTRING(Name, 2, LEN(Name))) AS ProperName
FROM Employees;

String Functions Quick Reference

Function	Purpose	Example
CONCAT(a, b)	Concatenate strings	CONCAT('A', 'B') → 'AB'
LEN(s)	String length	LEN('Hello') → 5
SUBSTRING(s, start, len)	Extract substring	SUBSTRING('Hello', 1, 3) → 'Hel'
REPLACE(s, old, new)	Replace text	REPLACE('AB', 'B', 'C') → 'AC'
TRIM(s)	Remove whitespace	TRIM(' A ') → 'A'
UPPER(s) / LOWER(s)	Case conversion	UPPER('abc') → 'ABC'
LEFT(s, n)	Get left n characters	LEFT('Hello', 2) → 'He'
RIGHT(s, n)	Get right n characters	RIGHT('Hello', 2) → 'lo'
CHARINDEX(find, s)	Find position	CHARINDEX('l', 'Hello') → 3
REVERSE(s)	Reverse string	REVERSE('ABC') → 'CBA'

---

Date Functions

Date functions are used for date and time data processing, including getting current time, date arithmetic, and formatting.

GETDATE / NOW - Get Current Time

-- SQL Server
SELECT GETDATE() AS CurrentDateTime;
-- Result: 2025-01-15 14:30:45.123

-- MySQL
SELECT NOW() AS CurrentDateTime;

-- PostgreSQL
SELECT CURRENT_TIMESTAMP AS CurrentDateTime;

-- Get date part only (SQL Server)
SELECT CAST(GETDATE() AS DATE) AS CurrentDate;
-- Result: 2025-01-15

DATEADD - Date Arithmetic

Add or subtract a specified time interval from a date.

-- Syntax
DATEADD(interval_unit, number, date)

-- Interval units: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

-- Example: Add 7 days
SELECT DATEADD(DAY, 7, '2025-01-15') AS Result;
-- Result: 2025-01-22

-- Example: Subtract 1 month
SELECT DATEADD(MONTH, -1, '2025-01-15') AS Result;
-- Result: 2024-12-15

-- Practical example: Calculate membership expiry date (1 year later)
SELECT
    MemberName,
    JoinDate,
    DATEADD(YEAR, 1, JoinDate) AS ExpiryDate
FROM Members;

-- Practical example: Query orders from last 30 days
SELECT * FROM Orders
WHERE OrderDate >= DATEADD(DAY, -30, GETDATE());

DATEDIFF - Date Difference Calculation

Calculate the difference between two dates.

-- Syntax
DATEDIFF(interval_unit, start_date, end_date)

-- Example: Calculate day difference
SELECT DATEDIFF(DAY, '2025-01-01', '2025-01-15') AS DaysDiff;
-- Result: 14

-- Example: Calculate month difference
SELECT DATEDIFF(MONTH, '2024-06-15', '2025-01-15') AS MonthsDiff;
-- Result: 7

-- Practical example: Calculate employee tenure
SELECT
    EmployeeName,
    HireDate,
    DATEDIFF(YEAR, HireDate, GETDATE()) AS YearsOfService
FROM Employees;

-- Practical example: Calculate order processing time
SELECT
    OrderID,
    OrderDate,
    ShipDate,
    DATEDIFF(DAY, OrderDate, ShipDate) AS ProcessingDays
FROM Orders;

DATEPART / YEAR / MONTH / DAY - Extract Date Parts

-- Using DATEPART
SELECT DATEPART(YEAR, '2025-01-15') AS Year;    -- 2025
SELECT DATEPART(MONTH, '2025-01-15') AS Month;  -- 1
SELECT DATEPART(DAY, '2025-01-15') AS Day;      -- 15
SELECT DATEPART(WEEKDAY, '2025-01-15') AS WeekDay;  -- 4 (Wednesday)

-- Using dedicated functions
SELECT YEAR('2025-01-15') AS Year;    -- 2025
SELECT MONTH('2025-01-15') AS Month;  -- 1
SELECT DAY('2025-01-15') AS Day;      -- 15

-- Practical example: Monthly sales statistics
SELECT
    YEAR(OrderDate) AS OrderYear,
    MONTH(OrderDate) AS OrderMonth,
    SUM(TotalAmount) AS MonthlySales
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY OrderYear, OrderMonth;

FORMAT / CONVERT - Date Formatting

Convert date to string in specified format.

-- SQL Server FORMAT function (more intuitive)
SELECT FORMAT(GETDATE(), 'yyyy-MM-dd') AS DateStr;
-- Result: 2025-01-15

SELECT FORMAT(GETDATE(), 'yyyy/MM/dd HH:mm:ss') AS DateTimeStr;
-- Result: 2025/01/15 14:30:45

SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy') AS LongDateStr;
-- Result: January 15, 2025

-- SQL Server CONVERT function (using style codes)
SELECT CONVERT(VARCHAR, GETDATE(), 23) AS DateStr;   -- 2025-01-15
SELECT CONVERT(VARCHAR, GETDATE(), 111) AS DateStr;  -- 2025/01/15
SELECT CONVERT(VARCHAR, GETDATE(), 112) AS DateStr;  -- 20250115

-- MySQL uses DATE_FORMAT
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') AS DateStr;
SELECT DATE_FORMAT(NOW(), '%M %d, %Y') AS LongDateStr;

Date Functions Quick Reference

Function	Purpose	Example
GETDATE()	Current datetime	GETDATE() → 2025-01-15 14:30:45
DATEADD(unit, n, date)	Date arithmetic	DATEADD(DAY, 7, '2025-01-15') → 2025-01-22
DATEDIFF(unit, d1, d2)	Date difference	DATEDIFF(DAY, '2025-01-01', '2025-01-15') → 14
YEAR(date)	Get year	YEAR('2025-01-15') → 2025
MONTH(date)	Get month	MONTH('2025-01-15') → 1
DAY(date)	Get day	DAY('2025-01-15') → 15
FORMAT(date, fmt)	Format date	FORMAT(GETDATE(), 'yyyy-MM-dd')

---

Math Functions

Math functions are used for numeric operations, including rounding, truncation, and absolute value.

ROUND - Rounding

-- Syntax
ROUND(number, decimal_places)

-- Examples
SELECT ROUND(3.14159, 2) AS Result;  -- 3.14
SELECT ROUND(3.145, 2) AS Result;    -- 3.15 (rounded)
SELECT ROUND(3.144, 2) AS Result;    -- 3.14

-- Round to integer
SELECT ROUND(3.7, 0) AS Result;      -- 4

-- Practical example: Round prices
SELECT
    ProductName,
    Price,
    ROUND(Price * 0.9, 0) AS DiscountPrice  -- 10% off, round to integer
FROM Products;

CEILING / FLOOR - Round Up and Round Down

-- CEILING: Round up (to larger integer)
SELECT CEILING(3.1) AS Result;   -- 4
SELECT CEILING(3.9) AS Result;   -- 4
SELECT CEILING(-3.1) AS Result;  -- -3

-- FLOOR: Round down (to smaller integer)
SELECT FLOOR(3.1) AS Result;     -- 3
SELECT FLOOR(3.9) AS Result;     -- 3
SELECT FLOOR(-3.1) AS Result;    -- -4

-- Practical example: Calculate boxes needed (12 items per box)
SELECT
    OrderID,
    Quantity,
    CEILING(Quantity / 12.0) AS BoxesNeeded
FROM OrderDetails;

ABS - Absolute Value

SELECT ABS(-10) AS Result;   -- 10
SELECT ABS(10) AS Result;    -- 10

-- Practical example: Calculate absolute price difference
SELECT
    ProductName,
    OriginalPrice,
    CurrentPrice,
    ABS(CurrentPrice - OriginalPrice) AS PriceDifference
FROM Products;

POWER / SQRT - Power and Square Root

-- POWER: Exponentiation
SELECT POWER(2, 3) AS Result;    -- 8 (2 to the power of 3)
SELECT POWER(10, 2) AS Result;   -- 100

-- SQRT: Square root
SELECT SQRT(16) AS Result;       -- 4
SELECT SQRT(2) AS Result;        -- 1.4142...

-- Practical example: Calculate compound interest
-- Principal 10000, annual rate 5%, 3 years future value
SELECT 10000 * POWER(1.05, 3) AS FutureValue;
-- Result: 11576.25

MOD / % - Remainder

-- SQL Server uses %
SELECT 10 % 3 AS Result;         -- 1

-- MySQL / PostgreSQL also support MOD function
SELECT MOD(10, 3) AS Result;     -- 1

-- Practical example: Determine odd or even
SELECT
    Number,
    CASE WHEN Number % 2 = 0 THEN 'Even' ELSE 'Odd' END AS Type
FROM Numbers;

---

Conversion Functions

Conversion functions are used for data type conversion and NULL value handling.

CAST - Type Conversion

Standard SQL type conversion function.

-- Syntax
CAST(expression AS target_type)

-- Number to string
SELECT CAST(123 AS VARCHAR(10)) AS Result;   -- '123'

-- String to number
SELECT CAST('456' AS INT) AS Result;         -- 456

-- String to date
SELECT CAST('2025-01-15' AS DATE) AS Result;

-- Practical example: Calculate average (avoid integer division)
SELECT CAST(SUM(Quantity) AS DECIMAL(10,2)) / COUNT(*) AS AvgQuantity
FROM OrderDetails;

CONVERT - Type Conversion (with Format)

SQL Server specific conversion function that supports date formatting.

-- Syntax
CONVERT(target_type, expression, style_code)

-- Number to string
SELECT CONVERT(VARCHAR(10), 123) AS Result;

-- Date formatting (common style codes)
SELECT CONVERT(VARCHAR, GETDATE(), 23) AS Result;   -- 2025-01-15 (ISO format)
SELECT CONVERT(VARCHAR, GETDATE(), 111) AS Result;  -- 2025/01/15
SELECT CONVERT(VARCHAR, GETDATE(), 112) AS Result;  -- 20250115
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Result;  -- 2025-01-15 14:30:45

Common Date Style Codes:

Code	Format	Example
23	yyyy-mm-dd	2025-01-15
111	yyyy/mm/dd	2025/01/15
112	yyyymmdd	20250115
120	yyyy-mm-dd hh:mi:ss	2025-01-15 14:30:45
108	hh:mi:ss	14:30:45

COALESCE - Return First Non-NULL Value

-- Syntax
COALESCE(value1, value2, value3, ...)

-- Examples
SELECT COALESCE(NULL, NULL, 'Hello') AS Result;  -- 'Hello'
SELECT COALESCE(NULL, 'A', 'B') AS Result;       -- 'A'

-- Practical example: Display default value
SELECT
    CustomerName,
    COALESCE(Phone, Mobile, 'No Phone') AS ContactPhone
FROM Customers;

-- Practical example: Calculate with default value
SELECT
    ProductName,
    Price * COALESCE(Discount, 1) AS FinalPrice
FROM Products;

ISNULL / IFNULL / NVL - NULL Value Replacement

-- SQL Server: ISNULL
SELECT ISNULL(NULL, 'Default Value') AS Result;  -- 'Default Value'

-- MySQL: IFNULL
SELECT IFNULL(NULL, 'Default Value') AS Result;

-- Oracle: NVL
SELECT NVL(NULL, 'Default Value') AS Result;

-- Practical example: Handle possibly NULL discount
SELECT
    ProductName,
    Price,
    ISNULL(Discount, 0) AS Discount,
    Price * (1 - ISNULL(Discount, 0)) AS FinalPrice
FROM Products;

---

Aggregate Functions

Aggregate functions perform summary calculations on multiple rows of data, typically used with GROUP BY.

COUNT - Count Rows

-- Count all rows
SELECT COUNT(*) AS TotalRows FROM Orders;

-- Count non-NULL values
SELECT COUNT(ShipDate) AS ShippedOrders FROM Orders;

-- Count distinct values
SELECT COUNT(DISTINCT CustomerID) AS UniqueCustomers FROM Orders;

-- Practical example: Employee count per department
SELECT
    DepartmentID,
    COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

SUM - Total

-- Basic sum
SELECT SUM(TotalAmount) AS TotalSales FROM Orders;

-- With condition
SELECT SUM(TotalAmount) AS JanuarySales
FROM Orders
WHERE MONTH(OrderDate) = 1;

-- Practical example: Sales by category
SELECT
    CategoryID,
    SUM(Price * Quantity) AS CategorySales
FROM OrderDetails od
JOIN Products p ON od.ProductID = p.ProductID
GROUP BY CategoryID;

AVG - Average

-- Basic average
SELECT AVG(Price) AS AvgPrice FROM Products;

-- Note: AVG ignores NULL values
-- To treat NULL as 0, convert first
SELECT AVG(ISNULL(Score, 0)) AS AvgScore FROM Students;

-- Practical example: Average salary per department
SELECT
    DepartmentID,
    AVG(Salary) AS AvgSalary,
    COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY DepartmentID;

MAX / MIN - Maximum and Minimum

-- Numeric max/min
SELECT MAX(Price) AS MaxPrice, MIN(Price) AS MinPrice FROM Products;

-- Date max/min
SELECT MAX(OrderDate) AS LatestOrder, MIN(OrderDate) AS EarliestOrder FROM Orders;

-- String max/min (alphabetical order)
SELECT MAX(ProductName) AS Last, MIN(ProductName) AS First FROM Products;

-- Practical example: Price range by category
SELECT
    CategoryID,
    MIN(Price) AS MinPrice,
    MAX(Price) AS MaxPrice,
    MAX(Price) - MIN(Price) AS PriceRange
FROM Products
GROUP BY CategoryID;

---

FAQ

Q1: How to convert date to string format in SQL?

There are multiple ways to convert dates to strings: (1) SQL Server FORMAT function: FORMAT(GETDATE(), 'yyyy-MM-dd') returns '2025-01-15', format codes match .NET, most intuitive; (2) SQL Server CONVERT function: CONVERT(VARCHAR, GETDATE(), 23) uses style codes, 23 represents ISO format yyyy-mm-dd; (3) MySQL DATE_FORMAT function: DATE_FORMAT(NOW(), '%Y-%m-%d'); (4) PostgreSQL TO_CHAR function: TO_CHAR(CURRENT_DATE, 'YYYY-MM-DD'). Recommendation: For SQL Server 2012+, use FORMAT (more intuitive); for older versions, use CONVERT.

Q2: What's the difference between CAST and CONVERT?

Both are type conversion functions with main differences: (1) Standards: CAST is ANSI SQL standard syntax, supported by all databases; CONVERT is SQL Server specific; (2) Date formatting: CONVERT supports style codes for date output format specification (e.g., CONVERT(VARCHAR, date, 23)); CAST doesn't support formatting, needs other functions; (3) Syntax: CAST uses AS keyword (CAST(x AS INT)), CONVERT uses comma (CONVERT(INT, x)). Recommendation: Use CAST for cross-database compatibility; use CONVERT or FORMAT for date formatting.

---

Further Learning Resources

For deeper SQL query skills, reference these resources:

SQL Basic Syntax:

• SQL Complete Guide: Database Query Language Tutorial from Beginner to Expert
• SQL Syntax Complete Guide: SELECT, JOIN, WHERE Query Commands Tutorial
• SQL JOIN Complete Tutorial: INNER, LEFT, RIGHT, FULL JOIN Illustrated
• SQL CRUD Operations Complete Guide: INSERT, UPDATE, DELETE Syntax Tutorial

Advanced Topics:

• SQL Window Function Tutorial: ROW_NUMBER, RANK, LAG Practical Applications
• SQL Subquery Complete Tutorial: Subquery Syntax and Practical Applications
• SQL Performance Tuning Complete Guide: Query Optimization and Index Strategy

---

Conclusion

SQL built-in functions are essential tools for database development. Mastering them can greatly improve development efficiency. The functions covered in this article address the most common daily development scenarios:

• String Functions: CONCAT, SUBSTRING, REPLACE, TRIM for text data processing
• Date Functions: DATEADD, DATEDIFF, FORMAT for date arithmetic and formatting
• Math Functions: ROUND, CEILING, FLOOR for numeric operations
• Conversion Functions: CAST, CONVERT, COALESCE for type conversion and NULL handling
• Aggregate Functions: COUNT, SUM, AVG, MAX, MIN for data summarization

We recommend bookmarking this article as a reference for quick lookups when you need to use functions.

---

Want to reference SQL functions anytime? — Download our organized SQL Functions Quick Reference PDF, including syntax and examples for all common functions, print it out and keep it by your desk for quick reference.

Free Download SQL Functions Quick Reference PDF →

---

References

• Microsoft SQL Server Functions Reference - https://docs.microsoft.com/sql/t-sql/functions/functions
• MySQL Functions Reference - https://dev.mysql.com/doc/refman/8.0/en/functions.html
• PostgreSQL Functions Reference - https://www.postgresql.org/docs/current/functions.html
• W3Schools SQL Functions - https://www.w3schools.com/sql/sql_ref_sqlserver.asp