Advanced String and Date Functions

Master powerful string manipulation and date formatting functions in PostgreSQL.

Overview

Beyond the basic string and date functions, PostgreSQL provides powerful tools for text formatting, parsing, regular expressions, and date arithmetic. REGEXP_REPLACE and REGEXP_MATCHES enable pattern-based text transformation. FORMAT() provides printf-style string interpolation. TO_TIMESTAMP and TO_DATE parse custom date formats. DATE_PART is an alias for EXTRACT. These functions are essential for data cleaning (normalising imported data), report generation (formatting output), and ETL pipelines (transforming and enriching data).

Key Points

•FORMAT(fmt, args) printf-style: FORMAT('%s has %s items', name, count)
•REGEXP_REPLACE(str, pattern, replacement) regex-based replace
•REGEXP_MATCHES(str, pattern) extract regex groups
•INITCAP(str) title case: 'hello world' → 'Hello World'
•LPAD/RPAD pad string to a fixed width
•DATE_PART is an alias for EXTRACT; both accept the same parts

Examples

Advanced string functions

SELECT FORMAT('%s has %s orders', name, order_count) FROM customer_summary;
SELECT INITCAP('hello world');            -- 'Hello World'
SELECT LPAD('42', 5, '0');               -- '00042'
SELECT REGEXP_REPLACE('  hello  world  ', '\s+', ' ', 'g');  -- 'hello world'
SELECT REGEXP_MATCHES('2024-06-15', '(\d{4})-(\d{2})-(\d{2})');

Advanced date functions

SELECT DATE_PART('year', NOW());         -- same as EXTRACT
SELECT TO_TIMESTAMP('2024-06-15 14:30', 'YYYY-MM-DD HH24:MI');
SELECT TO_CHAR(NOW(), 'Day, DD Month YYYY');
SELECT DATE_TRUNC('quarter', NOW());     -- first day of current quarter
SELECT NOW() AT TIME ZONE 'US/Eastern'; -- convert time zones

Advanced String and Date Functions

Overview

Key Points

Examples

Exercises

Knowledge Check

What does INITCAP('hello WORLD') return?