PostgreSQL String Functions

Concatenation

SELECT first_name || ' ' || last_name AS full_name FROM employees;

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM employees;
-- CONCAT() treats NULL as an empty string; || returns NULL if ANY operand is NULL

Case Conversion

SELECT UPPER(name), LOWER(name), INITCAP(name) FROM employees;
-- INITCAP capitalizes the first letter of each word: "rahul gupta" -> "Rahul Gupta"

Length

SELECT LENGTH(name) FROM employees;

Trimming Whitespace

SELECT TRIM('  hello  ');    -- 'hello'
SELECT LTRIM('  hello  ');   -- 'hello  '
SELECT RTRIM('  hello  ');   -- '  hello'
SELECT TRIM(BOTH 'x' FROM 'xxhelloxx');  -- 'hello' — trims a specific character, not just whitespace

Substrings

SELECT SUBSTRING(name FROM 1 FOR 3) FROM employees;
-- first 3 characters

SELECT SUBSTRING(email FROM '@(.*)$') FROM employees;
-- extract everything after @ using a regex pattern

Replacing Text

SELECT REPLACE(phone, '-', '') FROM employees;
-- strips all dashes out of a phone number

Splitting a String

SELECT SPLIT_PART(email, '@', 1) AS username FROM employees;
SELECT SPLIT_PART(email, '@', 2) AS domain FROM employees;

Padding

SELECT LPAD(id::TEXT, 5, '0') FROM employees;
-- pads the id with leading zeros: 42 -> "00042"

Checking for a Substring

SELECT * FROM employees WHERE POSITION('gupta' IN LOWER(email)) > 0;
-- equivalent to: WHERE email ILIKE '%gupta%'

Common Errors & Troubleshooting

• || returning NULL unexpectedly — if ANY operand in a || concatenation is NULL, the whole result is NULL; use CONCAT() (which treats NULL as empty) or wrap the nullable side in COALESCE(column, '') first.
• SUBSTRING/SPLIT_PART returning an empty string instead of erroring — these functions are forgiving about out-of-range positions (e.g. asking for part 3 of a string with only 2 delimited parts) — they return an empty string rather than throwing, which can mask a logic bug upstream.
• Off-by-one errors with SUBSTRING(... FROM x FOR y) — FROM is 1-based, like array indexes — SUBSTRING(name FROM 1 FOR 3) means "starting at the 1st character," not the 0th.

Key Takeaways

• || and CONCAT() both join strings, but || propagates NULL while CONCAT() treats NULL as empty.
• SPLIT_PART is the quick way to pull one piece out of a delimited string, like the domain from an email.
• For anything more elaborate than a fixed delimiter, SUBSTRING(... FROM 'pattern') supports full regex extraction.