String Functions

PostgreSQL has a rich set of built-in functions for working with text — concatenating, measuring, trimming, slicing, searching, and formatting strings directly in SQL. Doing this work in the database instead of pulling raw values into application code keeps reporting queries self-contained and often faster.

Concatenation: || and CONCAT()

The standard SQL concatenation operator is ||. PostgreSQL also provides a CONCAT() function, which has one notable advantage: it treats NULL arguments as empty strings instead of making the whole result NULL.

SQL

SELECT
  first_name || ' ' || last_name          AS full_name_operator,
  CONCAT(first_name, ' ', last_name)      AS full_name_function,
  CONCAT(first_name, ' ', NULL, last_name) AS null_safe_example
FROM customers;

full_name_operator | full_name_function | null_safe_example
-------------------+---------------------+-------------------
Ada Lovelace       | Ada Lovelace        | Ada Lovelace

Note

With ||, any NULL operand makes the entire concatenated result NULL. With CONCAT(), a NULL argument is simply skipped. Choose based on which behavior you actually want.

Length, case, and trimming

Function	Purpose
`LENGTH(text)`	Number of characters in the string
`UPPER(text)`	Converts to uppercase
`LOWER(text)`	Converts to lowercase
`TRIM(text)`	Removes leading and trailing whitespace (or a given character)
`LTRIM(text)`	Removes leading whitespace/characters only
`RTRIM(text)`	Removes trailing whitespace/characters only

SQL

SELECT
  LENGTH('PostgreSQL')          AS len,        -- 10
  UPPER('PostgreSQL')           AS upper_case,  -- POSTGRESQL
  LOWER('PostgreSQL')           AS lower_case,  -- postgresql
  TRIM('  padded text  ')       AS trimmed,     -- 'padded text'
  RTRIM('trailing---', '-')     AS rtrimmed;    -- 'trailing'

Extracting parts of a string

SUBSTRING() pulls out a piece of a string by position and length:

SQL

SELECT SUBSTRING('PostgreSQL', 1, 8);  -- 'Postgres'
SELECT SUBSTRING('PostgreSQL' FROM 9);  -- 'QL'

SPLIT_PART() is a genuinely handy PostgreSQL-specific function: it splits a string on a delimiter and returns just the piece at the given (1-based) position, without you needing to build an array first.

Extracting the domain from an email address

SQL

SELECT
  email,
  SPLIT_PART(email, '@', 1) AS local_part,
  SPLIT_PART(email, '@', 2) AS domain
FROM users;

email                | local_part | domain
---------------------+------------+-----------
ada@example.com      | ada        | example.com
grace@postgres.org   | grace      | postgres.org

Searching and replacing

SQL

SELECT POSITION('gre' IN 'PostgreSQL');        -- 5 (1-based index)
SELECT REPLACE('2024-01-15', '-', '/');         -- '2024/01/15'

POSITION(substring IN string) returns the 1-based index of the first occurrence, or 0 if not found. REPLACE(string, from, to) replaces every occurrence of from with to.

Formatting strings with FORMAT()

FORMAT() is PostgreSQL's sprintf-style string formatting function — useful for building readable messages or labels out of mixed values without a chain of || concatenations.

SQL

SELECT FORMAT('Order #%s for %s: $%s', order_id, customer_name, total)
FROM orders
JOIN customers ON customers.id = orders.customer_id;

Order #1042 for Ada Lovelace: $128.50

%s substitutes a value as a plain string; %I formats a value as a quoted SQL identifier and %L as a quoted SQL literal — both particularly useful when building dynamic SQL inside PL/pgSQL functions, where safely quoting user-supplied names and values matters.

Tip

Combine SPLIT_PART(), TRIM(), and LOWER() together and you can normalize a lot of messy, inconsistently-cased or padded text data directly in a query, without writing a single line of application code.

|| concatenates but propagates NULL; CONCAT() concatenates and skips NULL arguments.
LENGTH, UPPER/LOWER, and TRIM/LTRIM/RTRIM cover the everyday cleanup cases.
SUBSTRING() extracts by position; SPLIT_PART() extracts a piece of a delimited string by index.
REPLACE() swaps text; POSITION() finds it; FORMAT() builds formatted strings sprintf-style.