(opens in a new tab)

Postgres String Functions: REPLACE, SPLIT_PART

August 23, 2026 by Chat2DB Team

Most day-to-day SQL work on text comes down to a dozen operations: measure it, change case, trim it, pad it, find something in it, replace something in it, cut a piece out, or split it on a delimiter. PostgreSQL has a built-in function for each, but the names do not always match what you learned in MySQL or SQL Server, and a few (LENGTH vs OCTET_LENGTH, SPLIT_PART negative indexes, STRING_TO_TABLE) have version-specific behavior. This article is a practical tour of Postgres string functions, with a runnable example and result for every one, followed by notes on bulk updates, indexing for LOWER() and LIKE '%x%', migration equivalents, and a compact reference table. Everything applies to PostgreSQL 14 through 17 unless stated.

Sample data

All examples use this table. Paste it into Chat2DB, a free AI-powered SQL client (web version at https://app.chat2db.ai (opens in a new tab), desktop download at https://chat2db.ai/download (opens in a new tab)), or psql.

CREATE TABLE products (
  id    serial PRIMARY KEY,
  sku   text,
  name  text,
  tags  text,          -- comma-separated, legacy format
  url   text
);
 
INSERT INTO products (sku, name, tags, url) VALUES
  ('ELEC-001-US', '  Wireless Mouse  ', 'electronics,mouse,wireless', 'https://shop.example.com/p/1001'),
  ('ELEC-002-EU', 'USB-C Hub',          'electronics,usb,hub',        'https://shop.example.com/p/1002'),
  ('HOME-010-US', 'Café Kettle 1.7L',   'home,kitchen',               'http://shop.example.com/p/2010'),
  ('BOOK-100-UK', 'postgres internals', NULL,                         'https://books.example.org/b/3100');

Length: LENGTH, CHAR_LENGTH, OCTET_LENGTH

LENGTH and CHAR_LENGTH (alias CHARACTER_LENGTH) count characters; OCTET_LENGTH counts bytes. In a UTF-8 database they differ as soon as a non-ASCII character appears.

SELECT name,
       length(name)        AS chars,
       char_length(name)   AS chars2,
       octet_length(name)  AS bytes
FROM products WHERE id = 3;

The é takes two bytes in UTF-8. Pitfall: length(' x ') is 5, leading and trailing spaces count, and length(NULL) is NULL. For char(n) columns, trailing padding is ignored by length but counted by octet_length, one more reason to prefer text or varchar. bit_length also exists (bytes times 8).

Case: UPPER, LOWER, INITCAP

SELECT upper('usb-c hub'), lower('USB-C Hub'), initcap('postgres internals');
-- USB-C HUB | usb-c hub | Postgres Internals

INITCAP uppercases the first letter of every word and lowercases the rest; word boundaries are any non-letter/digit, so initcap('o''reilly') gives O'Reilly and initcap('usb-c') gives Usb-C. Case conversion is locale-aware for non-ASCII letters (upper('café') is CAFÉ under a UTF-8 locale). For case-insensitive search, do not LOWER() both sides blindly; see the indexing section below and the separate article on Postgres ILIKE.

Trimming: TRIM, LTRIM, RTRIM, BTRIM

TRIM removes a set of characters (default: space) from the start, end or both. BTRIM is the function-style equivalent of TRIM(BOTH ...).

SELECT '[' || trim(name) || ']'                         AS both_sides,    -- [Wireless Mouse]
       '[' || ltrim(name) || ']'                        AS left_only,     -- [Wireless Mouse  ]
       '[' || rtrim(name) || ']'                        AS right_only,    -- [  Wireless Mouse]
       trim(leading '0' from '000123')                  AS no_zeros,      -- 123
       btrim('xxhixx', 'x')                             AS btrim_x,       -- hi
       trim(both '-_' from '--_hello_--')               AS multi_chars    -- hello
FROM products WHERE id = 1;

Note that the second argument is a set of characters, not a string to strip: rtrim('file.txt', '.txt') returns file because it keeps removing any of ., t, x from the right. TRIM does not remove tabs or newlines unless you list them: trim(E' \t\n' from col).

Padding: LPAD, RPAD

SELECT lpad('42', 6, '0')   AS zero_padded,   -- 000042
       rpad('abc', 6, '.')  AS dotted,        -- abc...
       lpad('toolong', 4)   AS truncated;     -- tool

If the string is already longer than the target length, LPAD/RPAD truncate it. Zero-padding IDs for sorting is the most common use (lpad(id::text, 8, '0')).

Replacing: REPLACE, TRANSLATE, OVERLAY

REPLACE(string, from, to) replaces all occurrences of a substring, case-sensitively, and does not interpret patterns. For pattern-based replacement use regexp_replace (covered in the companion Postgres regex article).

SELECT replace(url, 'http://', 'https://') FROM products WHERE id = 3;
-- https://shop.example.com/p/2010
 
SELECT replace('a-b-c', '-', '');        -- abc
SELECT replace('Hello', 'hello', 'Bye'); -- Hello  (case-sensitive, no match)

TRANSLATE(string, from_chars, to_chars) maps characters one-to-one; characters in from with no counterpart in to are deleted. It is ideal for stripping or swapping sets of single characters in one pass:

SELECT translate('(415) 555-0101', '() -', '');     -- 4155550101  (delete 4 chars)
SELECT translate('2026/08/23', '/', '-');           -- 2026-08-23
SELECT translate('ÀÉÎÕÜ', 'ÀÉÎÕÜ', 'AEIOU');        -- AEIOU  (poor man's unaccent)

OVERLAY replaces by position rather than by content:

SELECT overlay('ELEC-001-US' PLACING 'EU' FROM 10 FOR 2);   -- ELEC-001-EU
SELECT overlay('1234567890' PLACING '***' FROM 4 FOR 3);    -- 123***7890

Finding: POSITION and STRPOS

Both return the 1-based index of the first occurrence, or 0 when not found (never NULL for non-null inputs). Only the argument order differs.

SELECT position('-' IN sku)      AS pos1,   -- 5
       strpos(sku, '-')          AS pos2,   -- 5
       position('zzz' IN sku)    AS missing -- 0
FROM products WHERE id = 1;

A common idiom is "everything before the first dash": left(sku, position('-' IN sku) - 1). Guard the not-found case, because position returning 0 makes that left(sku, -1), which silently drops the last character. SPLIT_PART (below) is usually the safer tool.

Cutting: SUBSTRING, SUBSTR, LEFT, RIGHT

SELECT substring(sku FROM 6 FOR 3)  AS std_form,   -- 001
       substr(sku, 6, 3)            AS func_form,  -- 001
       substring(sku, 6)            AS to_end,     -- 001-US
       left(sku, 4)                 AS prefix,     -- ELEC
       right(sku, 2)                AS suffix,     -- US
       left(sku, -3)                AS drop_last3, -- ELEC-001
       right(sku, -5)               AS drop_first5 -- 001-US
FROM products WHERE id = 1;

Positions are 1-based. Negative lengths in LEFT/RIGHT mean "all but the last/first n characters", which is handy and often unknown. SUBSTRING also accepts a regex (substring(col FROM '\d+')), which returns the first match.

Splitting: SPLIT_PART

SPLIT_PART(string, delimiter, n) returns the n-th field. If n exceeds the number of fields it returns an empty string, not NULL. Since PostgreSQL 14, n may be negative to count from the end.

SELECT sku,
       split_part(sku, '-', 1)  AS category,   -- ELEC
       split_part(sku, '-', 2)  AS number,     -- 001
       split_part(sku, '-', -1) AS region,     -- US   (PG14+)
       split_part(sku, '-', 9)  AS missing     -- ''   (empty string)
FROM products WHERE id = 1;
 
-- last path segment of a URL, regardless of depth
SELECT split_part(url, '/', -1) AS product_id FROM products;
-- 1001, 1002, 2010, 3100

The delimiter is a literal string, not a regex, and may be multi-character (split_part(s, ', ', 2)). An empty delimiter is an error.

Splitting into rows: STRING_TO_ARRAY + UNNEST, STRING_TO_TABLE

To split a string by delimiter into rows (normalizing a comma-separated column, for instance), convert to an array and unnest it:

SELECT p.id, trim(t.tag) AS tag
FROM products p
CROSS JOIN LATERAL unnest(string_to_array(p.tags, ',')) AS t(tag)
ORDER BY p.id, tag;

The NULL tags row (id 4) produces no rows because string_to_array(NULL, ',') is NULL. Use a LEFT JOIN LATERAL ... ON true if you need to keep it. string_to_array has a third argument that turns a given token into NULL: string_to_array('a,,c', ',', '') gives {a,NULL,c}.

PostgreSQL 14 added STRING_TO_TABLE, which does the split and the unnest in one call and is slightly cheaper for large inputs:

SELECT id, tag FROM products, string_to_table(tags, ',') AS tag;   -- PG14+

For variable separators (, or ; with optional spaces) use regexp_split_to_table. With ordinality you can keep positions: unnest(...) WITH ORDINALITY AS t(tag, pos).

Other everyday helpers: REVERSE, REPEAT, STARTS_WITH, CONCAT_WS, FORMAT

SELECT reverse('abc')                             AS rev,        -- cba
       repeat('=-', 4)                            AS rep,        -- =-=-=-=-
       starts_with(sku, 'ELEC')                   AS is_elec,    -- true
       concat_ws(' | ', sku, NULL, trim(name))    AS joined,     -- ELEC-001-US | Wireless Mouse
       concat('a', NULL, 'b')                     AS concat_fn,  -- ab
       'a' || NULL || 'b'                         AS pipe_null,  -- NULL
       format('%s costs %s (%I)', trim(name), 19.99, 'price usd') AS fmt
FROM products WHERE id = 1;
-- fmt: Wireless Mouse costs 19.99 ("price usd")

Two details matter here. CONCAT and CONCAT_WS skip NULL arguments, while the || operator propagates NULL; use coalesce or concat_ws when columns may be empty. FORMAT supports %s (string), %I (identifier, quoted when needed) and %L (literal, quoted and escaped), which makes it the right tool for building dynamic SQL in PL/pgSQL. starts_with(col, 'x') is equivalent to col LIKE 'x%' and left(col, 1) = 'x', and the planner can use a B-tree index for the LIKE 'x%' form under the C collation or text_pattern_ops.

Quoting and hashing: QUOTE_LITERAL, QUOTE_IDENT, MD5, ENCODE

SELECT quote_literal('O''Reilly')        AS lit,    -- 'O''Reilly'
       quote_ident('price usd')          AS ident,  -- "price usd"
       quote_ident('sku')                AS plain,  -- sku
       quote_nullable(NULL)              AS nul,    -- NULL
       md5('hello')                      AS hash,   -- 5d41402abc4b2a76b9719d911017c592
       encode('hello'::bytea, 'base64')  AS b64,    -- aGVsbG8=
       encode(sha256('hello'::bytea), 'hex') AS sha -- 2cf24dba5fb0a30e26e83b2ac5b9e29e1b161e5c1fa7425e73043362938b9824
;

quote_literal and quote_ident exist to build safe dynamic SQL; format('%L') and format('%I') are the modern shorthand. md5 returns hex text and is fine for fingerprints and change detection but not for passwords. encode/decode convert between bytea and hex, base64 or escape text.

Bulk updates with REPLACE

Changing a prefix or domain across a table is a single statement:

UPDATE products
SET url = replace(url, 'http://', 'https://')
WHERE url LIKE 'http://%';

Always include the WHERE clause: without it every row is rewritten (new tuple versions, WAL, bloat) even when replace changes nothing. Preview with SELECT url, replace(url, ...) FROM ... WHERE ... first, and on very large tables update in batches by primary-key range. For pattern-based edits (regexp_replace) the same rule applies; filter with the matching ~ operator.

Indexing and performance

Plain function calls in WHERE defeat ordinary indexes, but PostgreSQL lets you index the expression itself.

-- case-insensitive equality on name
CREATE INDEX products_name_lower ON products (lower(name));
SELECT * FROM products WHERE lower(name) = lower('usb-c hub');        -- uses the index
 
-- queries that always filter on the SKU category
CREATE INDEX products_sku_cat ON products (split_part(sku, '-', 1));
SELECT * FROM products WHERE split_part(sku, '-', 1) = 'ELEC';         -- uses the index

The expression in the query must match the index expression exactly (lower(name), not LOWER(TRIM(name))). For LIKE 'prefix%' a B-tree with text_pattern_ops works; for LIKE '%needle%', ILIKE and regex, install pg_trgm and build a GIN index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX products_name_trgm ON products USING gin (name gin_trgm_ops);
EXPLAIN SELECT * FROM products WHERE name ILIKE '%kettle%';
-- Bitmap Index Scan on products_name_trgm (on a table large enough for the planner to choose it)

Finally, prefer the cheapest function that does the job: position/strpos, replace, split_part and left/right are simple byte-level loops and are noticeably cheaper than their regex cousins in tight loops over millions of rows, although for most OLTP queries the difference is negligible compared with I/O.

Coming from MySQL or SQL Server

Two traps: MySQL LENGTH is bytes (Postgres length is characters), and MySQL/SQL Server default collations are case-insensitive while PostgreSQL's = and LIKE are case-sensitive.

Reference table

FAQ

How do I split a string by delimiter and get the last element?

Use a negative index: split_part(url, '/', -1) (PostgreSQL 14 and later). On older versions, reverse the string: reverse(split_part(reverse(url), '/', 1)), or take (string_to_array(url, '/'))[array_length(string_to_array(url, '/'), 1)].

Why does length() return a smaller number than my application reports?

Postgres length counts characters, while many client libraries (and MySQL LENGTH) count bytes. Compare with octet_length(). If the column is char(n), trailing blanks are also ignored by length.

What is the difference between POSITION and STRPOS, and which is faster?

They are the same operation with different argument order: position(substring IN string) is SQL-standard, strpos(string, substring) is the Postgres shorthand. Performance is identical; pick the one that reads better. Both return 0, not NULL, when the substring is missing.

Conclusion

PostgreSQL string functions cover the full workflow: length/octet_length to measure, upper/lower/initcap for case, trim and lpad/rpad for shape, replace/translate/overlay for edits, position/strpos to locate, substring/left/right to slice, and split_part, string_to_array with unnest, or string_to_table to split strings by delimiter into fields or rows. The few things that bite newcomers are NULL propagation with ||, characters versus bytes, split_part returning an empty string past the end, and the fact that function calls in WHERE need expression indexes or pg_trgm to stay fast. Keep the reference table handy, and when in doubt run the snippet against your own data in Chat2DB to see the result before you commit it to a migration.