String Functions

Real data is messy. Names arrive with inconsistent casing, phone numbers are formatted a dozen different ways, timestamps are full datetime values when you only care about the month. You could pull that data into application code and clean it there - but SQL's built-in functions let you do it right in the query.

This guide covers the most commonly used string and date functions in PostgreSQL. The concepts carry over to any SQL database; just the function names vary.

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 strips both ends, LTRIM strips the left only, RTRIM the right only. If you've ever had a WHERE clause return nothing because a stored name has a trailing space, you'll remember this one:

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 trick is NULL behavior. || returns NULL if any operand is NULL - so one missing middle name can silently wipe out the whole result. CONCAT treats NULL as an empty string instead. If your columns might be NULL, CONCAT is the safer pick.

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:

Date and time functions

NOW() and CURRENT_TIMESTAMP both return the current date and time with timezone. They behave the same in most queries:

SELECT NOW();  -- e.g., 2024-03-15 14:32:00+00

CURRENT_DATE gives you today's date with no time component - useful when you want to compare against a plain date column:

SELECT CURRENT_DATE;  -- e.g., 2024-03-15

EXTRACT pulls one unit out of a date or timestamp - year, month, day, hour, or anything else you need:

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 the precision you specify. This is the function you reach for when grouping by month, quarter, or week - it turns every timestamp in March into 2024-03-01 00:00:00, so GROUP BY collapses them into one row:

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 - add or subtract intervals directly from a date or timestamp:

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;

One subtlety: subtracting two timestamps gives you an interval, not a number. Subtracting two dates gives you an integer count of days. If you need a plain number from a timestamp difference, wrap it in EXTRACT.

A note on portability

Date functions are where SQL dialects diverge most. DATE_TRUNC is PostgreSQL. SQL Server has DATETRUNC (added more recently) or DATEADD/DATEPART. MySQL uses DATE_FORMAT and DATE_ADD. The concepts are universal - only the names change. When you switch databases, string and date functions are the first things to audit.