PostgreSQL String Functions: Examples and Practical Uses

Formulas & functions · Updated November 22, 2026

PostgreSQL string functions let you join, search, extract, clean, split, and format text values in SQL. Common starting points include concat or || for joining, substring and split_part for extracting, replace for literal substitutions, and regexp_replace for pattern-based changes.

What PostgreSQL string functions do

A string function takes text or a related value and returns a result, often another text value. These functions are useful in a query’s SELECT list, in filters, and in data-cleaning or reporting tasks. For example, a query can display a person’s name in a consistent case without changing the stored value, or extract a domain from an email address for a report.

PostgreSQL supports a broad set of built-in string functions and operators. The examples below use text values, but many operations also accept compatible character types such as varchar. Function names are not interchangeable: some handle literal text, while others interpret patterns or return arrays. Choose based on whether you want to transform, locate, or compare text.

SELECT lower('Hello, PostgreSQL!');
-- hello, postgresql!

The expression changes only the value returned by the query. It does not update a table unless used in a statement such as UPDATE.

Joining strings and handling NULL values

The || operator concatenates two values as text. It is concise and works well when both inputs are known to be non-NULL. The concat function joins any number of values, converting non-string values to text; it ignores NULL arguments. concat_ws means “concatenate with separator”: it inserts the first argument between later values and skips NULL values among those later arguments.

SELECT 'Postgre' || 'SQL' AS joined,
       concat('Order ', 42, ' confirmed') AS message,
       concat_ws(' ', 'Ada', NULL, 'Lovelace') AS full_name;

The results are PostgreSQL, Order 42 confirmed, and Ada Lovelace. In contrast, an expression such as 'Ada' || NULL evaluates to NULL. This difference matters when building display fields from optional columns. If the separator passed to concat_ws itself is NULL, the result is NULL; it is not treated as an ordinary value to skip.

For a name assembled from optional parts, concat_ws is often clearer than a chain of operators. It does not trim spaces already contained inside each value, though, so inconsistent source data may still need cleaning.

Changing case, length, and individual characters

lower and upper convert text to lowercase or uppercase according to the applicable locale and collation. initcap capitalizes the first letter of each word and lowercases other letters; that behavior may not match every language’s name-formatting conventions. These are transformations, not permanent changes to table data.

length and its alias char_length count characters in a text value. octet_length instead counts bytes, which can differ for characters encoded with multiple bytes. Neither count necessarily equals the number of user-perceived symbols: an accented character can be represented as a base character plus a combining mark. Test with the text and encoding relevant to your application if a user-interface character count is important.

SELECT length('café') AS characters,
       octet_length('café') AS bytes,
       left('PostgreSQL', 4) AS beginning,
       right('PostgreSQL', 3) AS ending;

In a UTF-8 database, the word in this example has four characters but five bytes. left and right return a specified number of characters from the start or end. For a position-based slice, use substring: substring('abcdef' from 2 for 3) returns bcd. Positions are one-based, so the first character is position 1.

Finding text and extracting pieces

Use position or strpos to locate a literal substring. Both return a one-based position, or 0 when the substring is not found. Their argument order differs: position('sql' in 'PostgreSQL') uses SQL’s in syntax, while strpos('PostgreSQL', 'sql') takes the full string first. Matching is case-sensitive unless you explicitly transform or compare the text another way.

substring extracts a portion by starting position and optional length. split_part extracts a field separated by a delimiter, which is convenient for predictable, simple formats.

SELECT position('SQL' in 'PostgreSQL') AS found_at,
       substring('abcdef' from 2 for 3) AS slice,
       split_part('north|central|south', '|', 2) AS region;

This returns position 8, bcd, and central. A common mistake is treating split_part as a general parser. It works for delimiter-separated text when the format is controlled, but it does not understand quoting rules in formats such as CSV. Use a proper parser or structured storage when delimiters can occur inside quoted fields.

Replacing text and using regular expressions

replace(string, from, to) changes literal occurrences of a substring. It does not interpret the search value as a regular expression. For example, replace('a.b.c', '.', '-') replaces each period with a hyphen. When the replacement depends on a pattern, use regexp_replace, which uses PostgreSQL’s regular-expression matching.

SELECT replace('a.b.c', '.', '-') AS literal_change,
       regexp_replace('Order 123', '[0-9]+', '###') AS pattern_change,
       regexp_replace('red   blue', '\s+', ' ', 'g') AS one_space;

The first result is a-b-c; the second is Order ###. The g flag in the third expression requests replacement of all matches, rather than only the first. Regular expressions are powerful but can be harder to read and maintain than literal functions. Use them when the pattern genuinely varies, and test edge cases such as empty input, punctuation, and repeated matches.

Related functions include regexp_match, which returns the first match as an array, and regexp_matches, which can return matching groups and, with the global flag, multiple matches as rows. These are useful when extraction depends on a pattern rather than a fixed delimiter. Their array or row results are different from the single text result produced by regexp_replace.

Trimming, padding, and cleaning text

trim, ltrim, rtrim, and the underlying btrim remove characters from both ends or one end of a string. With no character set specified, trimming removes spaces, not every possible whitespace character. When you supply a set of characters, PostgreSQL removes any of those characters from the edge; it does not remove the exact sequence as a unit.

SELECT trim('  report  ') AS clean,
       trim(both 'x' from 'xxxdataxxx') AS marked,
       lpad('42', 5, '0') AS padded;

The results are report, data, and 00042. The related lpad and rpad functions extend a string to a requested length using a fill string. If the input is already longer than the requested length, padding functions can truncate it, so avoid using them as a harmless formatting step without checking the target length.

Cleaning text for display is not the same as validating it. Trimming a phone number does not establish that it is a valid phone number, and lowercasing an email address should not be confused with a complete email-validation policy. Keep original input when normalization could discard meaningful information.

Splitting strings, arrays, and formatted output

string_to_array turns delimited text into an array, while array_to_string joins array elements with a delimiter. These are useful when data is already stored as a simple delimited value or when a query needs to work with an array. For example, string_to_array('red,green,blue', ',') produces a three-element array. If values can contain the delimiter, a plain split is not enough to recover the original fields reliably.

For composing output with placeholders, use format. Its %s placeholder formats a value as a string, %I quotes an SQL identifier, and %L quotes a literal. Identifier and literal quoting are especially useful when constructing dynamic SQL, but parameterized queries are generally preferable for values because they separate data from SQL syntax.

SELECT format('%s: %s', 'item', 42) AS label;
-- item: 42

Do not build SQL by directly concatenating untrusted user input. In procedural or dynamic SQL, use the database’s parameter facilities where available, and use the correct quoting mechanism for identifiers or literals when they must be inserted into a command. Quoting protects syntax; it is not a substitute for deciding which operations a user is allowed to request.

Text types, collations, and comparison behavior

PostgreSQL commonly uses text for variable-length strings; varchar is also variable-length and can enforce a declared maximum length. For most ordinary uses, choosing between these types is about data constraints and schema intent rather than a meaningful speed difference. The fixed-width char(n) type pads values with spaces, which can create surprising behavior in comparisons and display, so it is usually not the default choice for ordinary names or labels.

String functions do not automatically make every comparison case-insensitive. lower(value) = lower(other_value) is one way to compare after converting both sides, while ILIKE provides case-insensitive pattern matching. The result of case conversion and text ordering can depend on locale and collation settings. If a system needs consistent rules across environments, set and verify its database or column collation deliberately rather than assuming every locale behaves identically.

A related option is the citext extension, which provides a case-insensitive text type for comparisons. It can simplify some schemas, but it does not remove the need to understand collation and indexing behavior. Choose it only when case-insensitive comparison is the intended rule for that data.

Performance and practical selection

A string function in a filter can affect whether a regular index is useful. For example, a condition on lower(email) generally cannot use a plain index on email as though the expression were unchanged. If that lookup is important, a matching expression index can help:

CREATE INDEX users_email_lower_idx ON users (lower(email));
SELECT id FROM users WHERE lower(email) = lower('Ada@example.com');

The index expression and query expression should match in a way PostgreSQL can recognize, and the actual plan depends on table size, statistics, and query conditions. Check with EXPLAIN rather than assuming an index will be used. For substring searches such as ILIKE '%term%', a conventional B-tree index is generally not suitable; the pg_trgm extension offers trigram indexing options for many such searches.

  • Use || for straightforward concatenation when NULL behavior is acceptable.
  • Use concat_ws when joining optional values with a separator.
  • Use replace for literal substitutions and regular-expression functions for patterned text.
  • Use split_part for a known delimiter format, not as a general-purpose file parser.
  • Check query plans when functions appear in frequently used filters or joins.

Frequently Asked Questions

What is the difference between concat and || in PostgreSQL?
concat joins multiple values and ignores NULL arguments, while || concatenates operands and returns NULL if either operand is NULL. Choose according to the desired NULL behavior.

How do I remove spaces from both ends of a PostgreSQL string?
Use trim(value) or btrim(value). By default, these remove spaces from the ends. To remove a specified set of edge characters, use syntax such as trim(both 'x' from value).

How can I replace every occurrence of text?
Use replace(string, from, to) for a literal substring; it replaces occurrences throughout the string. Use regexp_replace with the g flag when the search is a regular-expression pattern.

Does PostgreSQL length count bytes or characters?
length and char_length count characters in text, whereas octet_length counts bytes. A character may occupy multiple bytes, and a visible symbol can comprise more than one character code point.

How do I extract text after a delimiter?
For a stable delimiter format, split_part(value, delimiter, field_number) returns a selected field. For example, the second field of 'a|b|c' split on '|' is b. Use a parser instead if the format permits escaped or quoted delimiters.

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.