String functions

Functions for concatenating, cleaning, slicing and searching text.

Syntax

a || b                      concat(a, b, ...)        concat_ws(sep, a, b, ...)
lower(s)  upper(s)  initcap(s)  length(s)
trim(s)  ltrim(s)  rtrim(s)  btrim(s, chars)
substring(s FROM start FOR len)  left(s, n)  right(s, n)
position(sub IN s)  strpos(s, sub)  replace(s, from, to)
split_part(s, delim, n)  lpad(s, len, fill)  rpad(...)
regexp_replace(s, pattern, repl [, flags])  string_agg(expr, sep ORDER BY ...)

|| returns NULL if any part is NULL; concat() / concat_ws() skip NULLs.
Positions are 1-based: substring('Riyadh' FROM 1 FOR 3) = 'Riy'.
trim(s) removes leading and trailing spaces; trim(BOTH 'x' FROM s) removes other characters.
length counts characters (not bytes; octet_length counts bytes).
split_part('a-b-c', '-', 2) = 'b'; a negative index counts from the end.
string_agg(col, ', ' ORDER BY col) is an aggregate that joins values from many rows into one string (NULLs are skipped).
For cleaning messy keys, normalise both sides the same way: lower(trim(email)).
NULL: almost every string function returns NULL for NULL input.

Examples

Cleaning and building strings shop

SELECT customer_id,
       lower(trim(email)) AS clean_email,
       initcap(lower(trim(city))) AS clean_city,
       concat_ws(' ', first_name, last_name) AS full_name
FROM customers
WHERE city <> trim(city) OR city <> initcap(city)
ORDER BY customer_id;

Finds the messy city spellings ('riyadh', 'Khobar ', 'JEDDAH') and shows the normalised value.

Slicing starter

SELECT email,
       split_part(email, '@', 1) AS local_part,
       split_part(email, '@', 2) AS domain,
       left(email, 3) AS first3,
       length(email) AS chars
FROM customers
WHERE email IS NOT NULL
ORDER BY customer_id
LIMIT 5;

split_part splits on the @ sign; positions are 1-based.

Aggregate strings starter

SELECT category, string_agg(product_name, ', ' ORDER BY product_name) AS products
FROM products
GROUP BY category
ORDER BY category;

One row per category with an alphabetical list of its products.

Pitfalls

Using + to concatenate (that is numeric addition in PostgreSQL).
with a NULL part turning the whole value into NULL.
Comparing or grouping uncleaned text ('Riyadh' vs 'riyadh ' count as different values).
Expecting 0-based positions.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: Concatenate with + (NULL-propagating) or CONCAT; LEN ignores trailing spaces; CHARINDEX instead of position; STRING_AGG(expr, sep) WITHIN GROUP (ORDER BY ...).
MySQL: is logical OR by default; use CONCAT (NULL-propagating) or CONCAT_WS. GROUP_CONCAT instead of string_agg; LENGTH counts bytes, CHAR_LENGTH characters.
Oracle: works and treats NULL as ''. LISTAGG(expr, sep) WITHIN GROUP (ORDER BY ...) instead of string_agg; INSTR instead of position.
BigQuery: and CONCAT both return NULL if any input is NULL; STRING_AGG(expr, sep ORDER BY ...).
Snowflake: and CONCAT; LISTAGG(expr, sep) WITHIN GROUP (ORDER BY ...).