PostgreSQL String Functions Guide

PostgreSQL has a rich set of string functions for transforming, searching, and formatting text data. Here are the ones you will use most often.

Concatenation

-- Using the || operator
SELECT first_name || ' ' || last_name AS full_name
FROM customers;

-- Using CONCAT (handles NULLs gracefully)
SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM customers;

-- CONCAT_WS joins with a separator
SELECT CONCAT_WS(', ', city, state, country) AS location
FROM customers;

The || operator returns NULL if any operand is NULL. CONCAT and CONCAT_WS skip NULLs, making them safer for optional fields.

Case Conversion and Trimming

SELECT
  UPPER('hello world'),       -- 'HELLO WORLD'
  LOWER('Hello World'),       -- 'hello world'
  INITCAP('hello world'),     -- 'Hello World'
  TRIM('  hello  '),          -- 'hello'
  LTRIM('  hello'),           -- 'hello'
  RTRIM('hello  ');           -- 'hello'

Extracting and Replacing

SELECT
  SUBSTRING('PostgreSQL' FROM 1 FOR 8),   -- 'PostgreS'
  LEFT('PostgreSQL', 4),                   -- 'Post'
  RIGHT('PostgreSQL', 3),                  -- 'SQL'
  REPLACE('Hello World', 'World', 'PG'),   -- 'Hello PG'
  LENGTH('PostgreSQL'),                    -- 10
  POSITION('SQL' IN 'PostgreSQL');         -- 8

Pattern Matching with Regex

-- Extract domain from email
SELECT
  email,
  SUBSTRING(email FROM '@(.+)$') AS domain
FROM customers;

-- Replace non-alphanumeric characters
SELECT REGEXP_REPLACE('Hello, World! 123', '[^a-zA-Z0-9]', '', 'g');
-- 'HelloWorld123'

-- Split a string into an array
SELECT REGEXP_SPLIT_TO_ARRAY('one,two,three', ',');
-- {one,two,three}

Common Use Cases

• Building full names or display labels from multiple columns
• Normalizing user input (trimming whitespace, standardizing case)
• Extracting parts of structured strings like emails or URLs
• Data cleaning during ETL or migration processes