PostgreSQL String Functions and Operators: Practical Guide

Formulas & functions · Updated November 22, 2026

PostgreSQL string functions and operators let you join, inspect, extract, search, and transform text in SQL. The main tools include the || concatenation operator, functions such as substring and replace, and pattern-matching operators such as LIKE and ~.

How PostgreSQL handles strings

PostgreSQL stores ordinary text in the text, varchar, and char types. For most application data, text is a practical default: it stores strings without a declared length limit. varchar(n) enforces a maximum character count, while char(n) is fixed-width and pads shorter values with spaces. These types can generally use the same string functions.

Most string positions are counted from 1, not 0. For example, the first character in 'Postgres' is at position 1. Also distinguish an empty string, '', from NULL: the former is a value containing no characters, while the latter represents missing or unknown data. That difference affects concatenation, filters, and function results.

PostgreSQL has many built-in functions for text work, so a query often needs no extension. The relevant function is usually determined by whether the task is joining values, changing their case, extracting a piece, or matching a pattern.

Concatenation and basic operators

The || operator joins text expressions. For example, first_name || ' ' || last_name creates a full name with a space between the values. PostgreSQL can also concatenate text with some non-array values by converting them to text, but explicit casts can make a query easier to understand when the conversion is important.

For joining several values, concat and concat_ws are often convenient. concat joins its arguments without a separator and ignores NULL arguments. concat_ws takes a separator first, then joins the remaining non-NULL arguments using it:

  • concat('order-', order_id) creates a label with a fixed prefix.
  • concat_ws(' ', first_name, middle_name, last_name) joins available name parts with spaces while skipping NULL parts.

There is a useful NULL distinction here: || propagates a NULL operand, so a concatenation containing a NULL typically evaluates to NULL. In contrast, concat skips NULL arguments. Choose based on whether missing input should invalidate the result or simply be omitted.

Inspecting and changing text

Several small functions answer common questions or make simple edits. char_length (also written length for text) counts characters, whereas octet_length counts bytes in the encoded representation. In a multibyte encoding such as UTF-8, a character can take more than one byte. A visible symbol made from a base letter and a combining mark may also count as multiple characters, even if it looks like one glyph.

  • lower(text) and upper(text) change letter case; initcap(text) capitalizes word starts.
  • replace(text, from, to) replaces every occurrence of a literal substring.
  • translate(text, from, to) maps individual characters by position, useful for character-by-character substitutions.
  • trim, ltrim, rtrim, and btrim remove characters from one or both ends.
  • repeat(text, count), reverse(text), lpad, and rpad repeat, reverse, or pad text.

Trimming a specified character set does not remove an exact multi-character phrase. For example, btrim('xyexampleyx', 'xy') removes any leading or trailing x or y characters until it reaches another character; it does not search for the substring xy as a unit. Use replace when the task is to remove a literal phrase wherever it occurs.

Extracting text and finding positions

substring extracts part of a string. Its common forms are substring(value FROM start FOR count) and substring(value, start, count). For example, substring('database' FROM 2 FOR 4) returns 'atab'. The start is one-based. If the count is omitted in the SQL-standard form, PostgreSQL returns the rest of the string from the requested position.

To find a literal substring, use position(needle IN text) or strpos(text, needle). Both return a one-based location, or 0 if the searched text is not present. This differs from functions that return NULL for a missing match, so check the documented return behavior before using the result in conditions.

split_part(text, delimiter, field_number) returns one field from a delimiter-separated string. For instance, split_part('west|central|east', '|', 2) returns 'central'. It is useful for simple, consistently delimited values, but it is not a general CSV parser: quoted fields can contain delimiters and need proper CSV handling. For structured data, consider whether the value belongs in separate columns or an appropriate structured type instead.

Searching with LIKE and regular expressions

LIKE matches an entire string against a pattern: % stands for any sequence of characters, and _ stands for exactly one character. Thus email LIKE '%@example.com' matches strings ending in that suffix. ILIKE performs a case-insensitive LIKE-style match under the applicable locale rules. If a pattern should treat a percent or underscore literally, specify an escape character with ESCAPE or escape it according to the chosen pattern.

For more expressive matching, PostgreSQL supports POSIX regular expressions. The ~ operator is case-sensitive, ~* is case-insensitive, and !~ and !~* are their non-match forms. For example, name ~ '^[A-Z] checks whether a name begins with an uppercase ASCII letter. A regular-expression pattern is not the same as a LIKE pattern: percent and underscore do not have LIKE’s wildcard meanings in a regular expression.

Functions provide more control than a yes-or-no match. regexp_replace(text, pattern, replacement) replaces a match; pass the g flag as the optional flags argument to replace all matches rather than only the first. regexp_matches can return matched portions, and regexp_split_to_array or regexp_split_to_table can divide text using a regular expression. Regular expressions are powerful, but a simple replace or split_part is usually clearer for literal, predictable input.

Building output and safe dynamic SQL

For formatted text, PostgreSQL’s format function uses placeholders. %s inserts a value as text, %I quotes a SQL identifier, and %L quotes a SQL literal. For example, format('Table: %I', table_name) safely formats a table name as an identifier. Positional placeholders such as %1$s let a value be reused or supplied in a different order.

Identifier quoting and value quoting are not interchangeable. When writing dynamic SQL, use %I for table or column names, but prefer query parameters or a procedural EXECUTE ... USING clause for data values. Do not concatenate untrusted input into SQL text: quoting mistakes can create errors or injection vulnerabilities. For ordinary display strings, concat or concat_ws may be simpler than format.

NULLs, encoding, and case behavior

String functions do not all treat NULL alike. Many functions return NULL when an input they need is NULL, while concat and concat_ws deliberately skip NULL value arguments. A NULL separator makes concat_ws return NULL. Use coalesce(value, '') when an empty string is specifically the desired replacement for a missing value; do not apply it automatically if missingness carries meaning.

Text operations also depend on encoding and collation. Character counts are not byte counts, and case conversion or case-insensitive comparisons can depend on locale and collation settings. For identifiers, account names, or other values with strict normalization rules, decide those rules explicitly and apply them consistently rather than assuming every language has the same casing behavior. PostgreSQL’s lower function changes case; it is not a universal substitute for a complete Unicode normalization or identity policy.

Choosing a function and avoiding common mistakes

Start with the simplest function that expresses the task. Literal replacement is easier to maintain with replace than with a regular expression; a fixed delimiter calls for split_part; optional pieces are often easiest to join with concat_ws. Use regex when the pattern itself varies or needs classes, repetition, or anchors.

  • Check whether NULL should propagate, be skipped, or be replaced.
  • Confirm whether a function counts characters or bytes and whether its positions start at 1.
  • Remember that LIKE patterns and regular-expression patterns use different syntax.
  • Use explicit casts when combining text with values whose text representation matters.
  • For frequent searches on large tables, test the query plan: a function or leading wildcard can prevent a conventional index from serving a condition efficiently. PostgreSQL offers specialized index options for some pattern searches, but the right choice depends on the query and data.

These distinctions matter in data cleaning and application queries. A string expression that looks correct for a few sample rows can behave differently with NULLs, non-ASCII text, an unexpected delimiter, or a pattern containing wildcard characters. Test representative edge cases before relying on it for imports or validation.

Frequently Asked Questions

What is the PostgreSQL string concatenation operator?
The operator is ||. It joins text expressions, but a NULL operand can make the result NULL. Use concat or concat_ws when NULL arguments should be skipped.

How do I get the length of a string in PostgreSQL?
Use char_length(value) or length(value) to count characters in text. Use octet_length(value) when you need the number of bytes instead.

How do I extract text after a delimiter?
For a fixed delimiter, use split_part(value, delimiter, field_number). If the delimiter appears more than once, choose the field number that identifies the part you want; for a variable pattern, consider a regular-expression split function.

What is the difference between LIKE and a regular expression?
LIKE uses only % and _ as its wildcard characters. POSIX regular expressions use a richer pattern syntax and the operators ~ or ~*. Choose LIKE for simple wildcard matching and regex for more complex rules.

Does PostgreSQL have a function to replace text?
Yes. replace(string, from, to) replaces literal occurrences. Use regexp_replace when the part to match is a regular-expression pattern rather than a fixed substring.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.