String Functions

Manipulate text with PostgreSQL's built-in string functions.

Overview

PostgreSQL provides a rich set of string functions for transforming, inspecting, and extracting from text values. These are essential for cleaning imported data, formatting output, and building search functionality. The most commonly used are UPPER, LOWER, LENGTH, TRIM, SUBSTRING, REPLACE, CONCAT, and SPLIT_PART. String functions are typically used in SELECT for display transformation, in WHERE for case-insensitive matching, and in UPDATE for bulk data cleaning. Remember: string functions in WHERE prevent index usage unless you create a functional index.

Key Points

•UPPER(str) / LOWER(str) change case
•LENGTH(str) / CHAR_LENGTH(str) character count
•TRIM(str) / LTRIM / RTRIM remove whitespace (or specified characters)
•SUBSTRING(str FROM start FOR length) extract substring
•REPLACE(str, from, to) replace all occurrences
•CONCAT(a, b) or a || b concatenate strings
•SPLIT_PART(str, delimiter, n) split and return nth part

Examples

Common string operations

SELECT UPPER('hello world');          -- 'HELLO WORLD'
SELECT LOWER('ALICE');               -- 'alice'
SELECT LENGTH('PostgreSQL');         -- 10
SELECT TRIM('  hello  ');            -- 'hello'
SELECT LTRIM('  left');              -- 'left'
SELECT RTRIM('right  ');             -- 'right'

Extracting and transforming

-- Substring
SELECT SUBSTRING('PostgreSQL 16' FROM 1 FOR 10);  -- 'PostgreSQL'
SELECT LEFT('hello', 3);     -- 'hel'
SELECT RIGHT('hello', 3);    -- 'llo'

-- Split an email to get domain
SELECT SPLIT_PART('alice@example.com', '@', 2);  -- 'example.com'

-- Concatenation
SELECT 'Hello' || ', ' || 'World' || '!';  -- 'Hello, World!'
SELECT CONCAT('Hello', ', ', 'World');      -- same result

String Functions

Overview

Key Points

Examples

Exercises

Knowledge Check

What does SPLIT_PART('a.b.c', '.', 2) return?