How do I use SQL string, number, and date functions?

SQL databases store raw values, but real reports rarely display them exactly as stored. Names may need to be capitalized, prices rounded, and dates formatted in a friendlier way. SQL provides built-in functions that transform values directly inside a query, so the database returns exactly what your application or report needs.

In this tutorial, you will learn how to use the most common SQL string, number, and date functions. You will see how each category works, when to use it, and how the syntax may differ between database systems.

Prerequisites

To follow along, you should be comfortable with:

Writing basic SELECT statements.
Filtering rows with WHERE.
Sorting results with ORDER BY.

You also need a running database (PostgreSQL, MySQL, MariaDB, SQL Server, Oracle Database, or SQLite) where you can create a small sample table.

Sample Database

We will continue using the online bookstore domain from earlier tutorials. For this article, a single books table is enough.

CREATE TABLE books (
    book_id      INT PRIMARY KEY,
    title        VARCHAR(200) NOT NULL,
    author_name  VARCHAR(100) NOT NULL,
    price        DECIMAL(8, 2) NOT NULL,
    published_at DATE NOT NULL
);

INSERT INTO books (book_id, title, author_name, price, published_at) VALUES
    (1, 'Clean Code',              'Robert C. Martin',   35.499, DATE '2008-08-01'),
    (2, 'Effective Java',          'Joshua Bloch',       42.000, DATE '2018-01-06'),
    (3, 'The Pragmatic Programmer','Andrew Hunt',        39.950, DATE '1999-10-20'),
    (4, 'Refactoring',             'martin fowler',      45.750, DATE '2018-11-30'),
    (5, 'Domain-Driven Design',    'Eric Evans',         50.000, DATE '2003-08-22');

Note: The literal DATE '2008-08-01' is standard SQL. In MySQL and SQL Server you can also write '2008-08-01' directly.

What Is a SQL Function?

A function takes one or more input values and returns a single output value. Functions can appear almost anywhere a value can appear: in SELECT, WHERE, ORDER BY, and so on.

SELECT UPPER(title) AS title_upper
FROM books;

There are two broad categories worth knowing early:

Scalar functions operate on one row at a time (the focus of this tutorial).
Aggregate functions such as SUM, COUNT, and AVG operate on groups of rows and are covered in a later tutorial.

String Functions

String functions transform text values. The most commonly used ones include:

Function	Purpose
`UPPER(text)`	Convert to uppercase.
`LOWER(text)`	Convert to lowercase.
`LENGTH(text)`	Return the number of characters.
`TRIM(text)`	Remove leading and trailing spaces.
`SUBSTRING(text FROM a FOR b)`	Extract part of a string.
`REPLACE(text, from, to)`	Replace occurrences of a substring.
Concatenation	Combine two or more strings.

Example: Normalize Names and Show Title Length

SELECT
    book_id,
    UPPER(title)                AS title_upper,
    LOWER(author_name)          AS author_lower,
    LENGTH(title)               AS title_length
FROM books
ORDER BY book_id;

Expected result:

book_id	title_upper	author_lower	title_length
1	CLEAN CODE	robert c. martin	10
2	EFFECTIVE JAVA	joshua bloch	14
3	THE PRAGMATIC PROGRAMMER	andrew hunt	24
4	REFACTORING	martin fowler	11
5	DOMAIN-DRIVEN DESIGN	eric evans	20

Example: Concatenate Values

String concatenation is one of the areas where SQL dialects differ the most.

Portable (ANSI SQL, PostgreSQL, Oracle Database, SQLite):

SELECT title || ' by ' || author_name AS display_label
FROM books;

MySQL and MariaDB:

SELECT CONCAT(title, ' by ', author_name) AS display_label
FROM books;

SQL Server:

SELECT title + ' by ' + author_name AS display_label
FROM books;

CONCAT(...) is also supported by PostgreSQL, SQL Server, and Oracle Database, and is often the safest choice when you want a single function name across systems.

Number Functions

Number functions perform math on numeric columns. Common ones include:

Function	Purpose
`ABS(number)`	Absolute value.
`ROUND(number, digits)`	Round to a given number of decimals.
`CEIL(number)` / `CEILING(number)`	Round up to the next integer.
`FLOOR(number)`	Round down to the previous integer.
`MOD(a, b)` or `a % b`	Remainder after division.
`POWER(a, b)`	Raise `a` to the power `b`.

Example: Round Prices and Apply a Discount

SELECT
    book_id,
    title,
    price                              AS original_price,
    ROUND(price, 2)                    AS price_rounded,
    ROUND(price * 0.90, 2)             AS price_after_10_percent_off,
    CEIL(price)                        AS price_ceiling,
    FLOOR(price)                       AS price_floor
FROM books
ORDER BY book_id;

Expected result:

book_id	title	original_price	price_rounded	price_after_10_percent_off	price_ceiling	price_floor
1	Clean Code	35.499	35.50	31.95	36	35
2	Effective Java	42.000	42.00	37.80	42	42
3	The Pragmatic Programmer	39.950	39.95	35.96	40	39
4	Refactoring	45.750	45.75	41.18	46	45
5	Domain-Driven Design	50.000	50.00	45.00	50	50

Notes:

CEIL is called CEILING in SQL Server.
Rounding half-away-from-zero versus banker’s rounding may differ by database. Consult your database documentation when exact rounding rules matter, especially for money.

Date and Time Functions

Date functions extract, compute, or format temporal values. Common tasks include getting the current date, extracting a year, or computing the difference between two dates.

Frequently used functions:

Task	PostgreSQL / ANSI	MySQL	SQL Server
Current date	`CURRENT_DATE`	`CURDATE()`	`CAST(GETDATE() AS DATE)`
Current timestamp	`CURRENT_TIMESTAMP`	`NOW()`	`GETDATE()` / `SYSDATETIME()`
Extract year	`EXTRACT(YEAR FROM published_at)`	`YEAR(published_at)`	`YEAR(published_at)`
Extract month	`EXTRACT(MONTH FROM published_at)`	`MONTH(published_at)`	`MONTH(published_at)`
Add days	`published_at + INTERVAL '7 days'`	`DATE_ADD(published_at, INTERVAL 7 DAY)`	`DATEADD(day, 7, published_at)`
Difference in days	`(a - b)`	`DATEDIFF(a, b)`	`DATEDIFF(day, b, a)`

Tip: EXTRACT is defined by the SQL standard and works in PostgreSQL, MySQL 8+, MariaDB, and Oracle Database. Prefer it when portability matters.

Example: Show Publication Year and Age in Years

Portable version using EXTRACT:

SELECT
    book_id,
    title,
    published_at,
    EXTRACT(YEAR FROM published_at)                       AS published_year,
    EXTRACT(YEAR FROM CURRENT_DATE)
        - EXTRACT(YEAR FROM published_at)                 AS age_in_years
FROM books
ORDER BY published_at;

Expected result (as of 2026):

book_id	title	published_at	published_year	age_in_years
3	The Pragmatic Programmer	1999-10-20	1999	27
5	Domain-Driven Design	2003-08-22	2003	23
1	Clean Code	2008-08-01	2008	18
2	Effective Java	2018-01-06	2018	8
4	Refactoring	2018-11-30	2018	8

Note: age_in_years computed by subtracting years is an approximation. If a book was published later in the year than today’s date, the true age is one year less. Precise age calculations require additional logic and are covered in an intermediate tutorial.

Example: Filter Books Published in the Last 10 Years

PostgreSQL:

SELECT title, published_at
FROM books
WHERE published_at >= CURRENT_DATE - INTERVAL '10 years'
ORDER BY published_at DESC;

MySQL:

SELECT title, published_at
FROM books
WHERE published_at >= DATE_SUB(CURDATE(), INTERVAL 10 YEAR)
ORDER BY published_at DESC;

SQL Server:

SELECT title, published_at
FROM books
WHERE published_at >= DATEADD(YEAR, -10, CAST(GETDATE() AS DATE))
ORDER BY published_at DESC;

Combining Functions

Functions can be nested to build more expressive queries. Combining string and date functions is a common pattern for building readable labels.

PostgreSQL / Oracle Database / SQLite:

SELECT
    UPPER(TRIM(title))
        || ' ('
        || CAST(EXTRACT(YEAR FROM published_at) AS VARCHAR(4))
        || ')' AS display_label
FROM books
ORDER BY published_at;

MySQL:

SELECT
    CONCAT(UPPER(TRIM(title)), ' (', YEAR(published_at), ')') AS display_label
FROM books
ORDER BY published_at;

Expected result (values are the same regardless of dialect):

display_label
THE PRAGMATIC PROGRAMMER (1999)
DOMAIN-DRIVEN DESIGN (2003)
CLEAN CODE (2008)
EFFECTIVE JAVA (2018)
REFACTORING (2018)

Common Mistakes

Assuming a Function Exists in Every Database

Not every database supports the same function name. For example, LEN exists in SQL Server, but the portable equivalent is LENGTH (or CHAR_LENGTH for character counts on multibyte strings). Always check the reference for your database version before assuming a function is available.

Applying a Function to an Indexed Column in `WHERE`

Wrapping an indexed column in a function often prevents the database from using its index efficiently.

Slower:

SELECT title
FROM books
WHERE YEAR(published_at) = 2018;

Usually faster and index-friendly:

SELECT title
FROM books
WHERE published_at >= DATE '2018-01-01'
  AND published_at <  DATE '2019-01-01';

Measure with EXPLAIN before assuming one form is always faster; execution-plan syntax and output vary between database systems.

Comparing Strings with Inconsistent Case

'Java' and 'java' may or may not compare as equal, depending on the database and the column’s collation. When case-insensitive comparisons are required, normalize both sides:

SELECT title
FROM books
WHERE LOWER(author_name) = LOWER('Martin Fowler');

Be aware that this may still bypass indexes; consider using a case-insensitive collation or a functional index when supported.

Confusing `NULL` Behavior

Most functions return NULL when given a NULL argument. For example, LENGTH(NULL) returns NULL, not 0. Use COALESCE to substitute a default:

SELECT COALESCE(LENGTH(author_name), 0) AS name_length
FROM books;

Database Compatibility

PostgreSQL: Rich set of standard-compliant functions. Supports || concatenation, EXTRACT, and INTERVAL arithmetic.
MySQL / MariaDB: Provide CONCAT, YEAR, MONTH, DATE_ADD, DATE_SUB, NOW, CURDATE. Note that || is logical OR unless PIPES_AS_CONCAT mode is enabled.
SQL Server: Uses + for string concatenation, LEN for string length, GETDATE, DATEADD, DATEDIFF. Also supports CONCAT.
Oracle Database: Supports || concatenation, SUBSTR, LENGTH, TO_CHAR, ADD_MONTHS, and SYSDATE.
SQLite: Provides a smaller set of functions. Dates are typically stored as text or numbers and manipulated with DATE, STRFTIME, and DATETIME.

When in doubt, check your database version’s official reference.

Best Practices

Prefer standard functions such as EXTRACT and CONCAT when they exist in your target databases.
Avoid wrapping indexed columns in functions inside WHERE when equivalent range predicates exist.
Format values in the application layer when possible; use SQL functions when the transformation belongs to the query result (for example, aggregation keys or grouping).
Keep expressions readable: alias every derived column with AS.
Be explicit about types when combining functions, especially when mixing strings, numbers, and dates.

Conclusion

You learned how to use the most common SQL string, number, and date functions to transform values directly inside a query, and how their syntax varies between PostgreSQL, MySQL, SQL Server, Oracle Database, and SQLite. Use these functions to shape query results into exactly the form your application or report needs, and remember that applying functions to indexed columns can affect performance.