String and Date Functions
The most useful built-in functions for working with text and time.
Data rarely arrives in the exact shape you need it in. Names need to be normalized, phone numbers need area codes extracted, timestamps need to be truncated to a month. SQL's built-in functions handle these transformations directly in your query, without needing to pull the data into application code first.
This guide covers the most commonly used string and date functions in PostgreSQL. The concepts are universal; the specific function names vary somewhat across database systems.
String functions
UPPER and LOWER normalize capitalization. Useful for case-insensitive comparisons and consistent display:
SELECT UPPER(name), LOWER(email) FROM customers;
LENGTH returns the number of characters in a string:
SELECT name, LENGTH(name) AS name_length FROM customers;
TRIM, LTRIM, RTRIM remove whitespace. TRIM removes from both ends, LTRIM from the left only, RTRIM from the right only. This is essential when cleaning up user-entered data:
SELECT TRIM(name) FROM customers;
SUBSTRING extracts part of a string by position:
SELECT SUBSTRING(phone, 1, 3) AS area_code FROM customers;
-- Arguments: string, start position (1-indexed), length
REPLACE swaps every occurrence of one substring for another:
SELECT REPLACE(description, 'old_term', 'new_term') FROM products;
CONCAT and || join multiple strings together. PostgreSQL supports both:
SELECT first_name || ' ' || last_name AS full_name FROM employees;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
The key difference: || returns NULL if any operand is NULL, while CONCAT treats NULL as an empty string. If any of your name columns can be NULL, CONCAT is the safer choice.
POSITION finds where a substring first appears:
SELECT POSITION('@' IN email) AS at_sign_position FROM customers;
-- Returns 0 if not found
A quick reference for the functions above:
| Function | Purpose | Returns NULL if input is NULL? |
|---|---|---|
UPPER(s) | Uppercase | Yes |
LOWER(s) | Lowercase | Yes |
LENGTH(s) | Character count | Yes |
TRIM(s) | Remove whitespace | Yes |
SUBSTRING(s, n, len) | Extract part | Yes |
REPLACE(s, from, to) | Swap substring | Yes |
CONCAT(a, b, ...) | Join strings | No (NULLs become empty) |
| `a | b` |
Date and time functions
NOW() and CURRENT_TIMESTAMP return the current date and time, including timezone:
SELECT NOW(); -- e.g., 2024-03-15 14:32:00+00
CURRENT_DATE returns today's date with no time component:
SELECT CURRENT_DATE; -- e.g., 2024-03-15
EXTRACT pulls a single component out of a date or timestamp:
SELECT EXTRACT(YEAR FROM hire_date) AS hire_year FROM employees;
SELECT EXTRACT(MONTH FROM created_at) AS month FROM orders;
SELECT EXTRACT(DOW FROM created_at) AS weekday FROM orders; -- 0=Sunday
DATE_TRUNC rounds a timestamp down to a given precision. This is especially useful for grouping by time period:
SELECT DATE_TRUNC('month', created_at) AS month, COUNT(*) AS orders
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;
Common precision values: 'second', 'minute', 'hour', 'day', 'week', 'month', 'quarter', 'year'.
Date arithmetic using intervals:
SELECT hire_date + INTERVAL '90 days' AS probation_end FROM employees;
SELECT created_at + INTERVAL '1 year' AS renewal_date FROM subscriptions;
SELECT NOW() - created_at AS account_age FROM users;
Subtracting two timestamps returns an interval. Subtracting two dates returns an integer number of days.
A note on portability
Date functions are where SQL dialects diverge most. DATE_TRUNC is PostgreSQL-specific; SQL Server uses DATETRUNC (added later) or DATEADD/DATEPART; MySQL has DATE_FORMAT and DATE_ADD. The concepts are identical; only the syntax changes. When moving between databases, the string and date functions are the first place to check for incompatibilities.