PostgreSQL - String Functions

PostgreSQL: Modern Guide to String Functions

Manipulating text is a common task in database management. PostgreSQL offers a comprehensive set of functions for processing and transforming string data. Instead of a simple list, let’s group them by their purpose for easier learning.

For our examples, let’s assume we have a users table:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    full_name VARCHAR(100)
);

INSERT INTO users (username, email, full_name) VALUES
('jdoe', 'john.doe@example.com', '  John Doe  '),
('asmith', 'anna.smith@web.dev', 'Anna Smith'),
('pg_user', 'user@postgresql.org', 'PostgreSQL User');

1. Concatenation: Combining Strings

Combining strings is one of the most frequent operations.

Function/Operator	Description	Example
||	The standard SQL concatenation operator. Preferred for its simplicity.	SELECT ‘Hello’ || ’ ’ || ‘World’; — Returns ‘Hello World’
CONCAT(str1, str2, …)	A function that concatenates multiple arguments. It conveniently ignores NULL arguments.	SELECT CONCAT(full_name, ’ <’, email, ’>’) FROM users WHERE id = 2; — ‘Anna Smith anna.smith@web.dev’
CONCAT_WS(sep, str1, …)	Concatenates with a separator. The first argument is the separator to place between other arguments.	SELECT CONCAT_WS(’ ’, ‘PostgreSQL’, ‘is’, ‘awesome’); — ‘PostgreSQL is awesome’

2. Changing Case and Trimming Spaces

Cleaning up user input often involves standardizing case and removing extra whitespace.

Function	Description	Example
LOWER(str)	Converts the string to all lowercase.	SELECT LOWER(‘PostgreSQL’); — ‘postgresql’
UPPER(str)	Converts the string to all uppercase.	SELECT UPPER(‘PostgreSQL’); — ‘POSTGRESQL’
INITCAP(str)	Converts the first letter of each word to uppercase and the rest to lowercase.	SELECT INITCAP(‘hello world’); — ‘Hello World’
TRIM([BOTH|LEADING|TRAILING] [chars] FROM str)	Removes leading and/or trailing characters (spaces by default).	SELECT TRIM(full_name) FROM users WHERE id = 1; — ‘John Doe’

3. Substrings and Splitting

Extracting parts of a string is essential for parsing data.

Function	Description	Example
SUBSTRING(str FROM pos FOR len)	Extracts a substring. The FOR len part is optional.	SELECT SUBSTRING(‘PostgreSQL’ FROM 1 FOR 4); — ‘Post’
LEFT(str, n) / RIGHT(str, n)	Convenience functions to get the first or last n characters.	SELECT LEFT(email, 4) FROM users WHERE id = 1; — ‘john’
SPLIT_PART(str, delim, field)	Splits a string by a delimiter and returns the Nth part (1-indexed).	SELECT SPLIT_PART(email, ’@’, 2) FROM users WHERE id = 1; — ‘example.com’
POSITION(substr IN str)	Finds the starting position of a substring.	SELECT POSITION(’@’ IN email) FROM users WHERE id = 1; — 9

4. Replacing and Getting Length

Function	Description	Example
REPLACE(str, from, to)	Replaces all occurrences of a substring with another.	SELECT REPLACE(‘one two one’, ‘one’, ‘three’); — ‘three two three’
LENGTH(str)	Returns the number of characters in the string.	SELECT LENGTH(username) FROM users WHERE id = 3; — 7
CHAR_LENGTH(str)	Synonym for LENGTH().	SELECT CHAR_LENGTH(username) FROM users WHERE id = 3; — 7

5. Advanced: Regular Expressions and Formatting

For complex pattern matching and formatting, PostgreSQL provides powerful tools.

• Regular Expressions: Use the ~ operator for matching (email ~ '.+\.com$'), REGEXP_REPLACE for advanced replacement, and REGEXP_MATCHES for extracting matched groups.
• FORMAT(format_str, …): A powerful function for building formatted strings, similar to printf in other languages. It’s safer and often cleaner than complex concatenation. Example: SELECT FORMAT('User %s has ID %s.', full_name, id) FROM users WHERE id = 2;

Mini-Project: Generating User Profile Data

Let’s combine some functions to generate a clean profile summary:

SELECT
    TRIM(full_name) AS cleaned_name,
    LOWER(username) AS username_lower,
    SPLIT_PART(email, '@', 1) AS email_user,
    SPLIT_PART(email, '@', 2) AS email_domain
FROM users
WHERE id = 1;

This query demonstrates cleaning data (TRIM), standardizing (LOWER), and parsing (SPLIT_PART) in a single, practical statement.