PostgreSQL string functions let you join, inspect, extract, search, and transform text directly in SQL. Common choices include concat and || for combining values, substring and split_part for extracting pieces, and replace or regular-expression functions for changing text.
What string functions do in PostgreSQL
PostgreSQL stores ordinary character data primarily as text or varchar, and its built-in string functions work with these types in many combinations. They can clean user input, prepare labels for reports, extract fields from a known text pattern, and support searching or validation. A function typically takes one or more values and returns a value, so it can be used in a SELECT list, a filter, an update, or another expression.
For example, this query makes a display name from two columns:
SELECT concat_ws(' ', first_name, last_name) AS display_name
FROM customers;
PostgreSQL has a large catalog of string-related functions, operators, and pattern-matching features. The right choice depends on whether the task is literal text manipulation, splitting, searching, formatting, or matching a pattern. For a one-off transformation, a built-in function is often clear and compact; for complex parsing, application code or a more structured database design may be easier to maintain.
Text types and character length
text holds variable-length text, as does varchar unless a maximum length is specified. PostgreSQL does not generally make varchar(n) faster than text; use a length limit when it represents a real data rule, not as a routine performance optimization. The fixed-length char(n) type pads values with spaces and has special trailing-space behavior, so it is usually not the best default for ordinary text.
Several length functions answer different questions:
char_length(value)andlength(value)return the number of characters in a string.octet_length(value)returns the number of bytes used by the string in the database encoding.bit_length(value)returns the number of bits represented by the string data.
For example, length('café') counts characters, while octet_length('café') can return a larger number because some characters take multiple bytes in UTF-8. Character length is not necessarily the number of visible symbols: a letter combined with a separate accent mark can contain more than one code point. If a user-facing limit concerns perceived characters, test the exact behavior needed rather than assuming length counts visual graphemes.
Combining strings and handling NULL
The concatenation operator || joins text values. The functions concat and concat_ws are useful when some inputs may be null or when a separator is needed. Their null handling differs, which is a frequent source of surprises.
SELECT 'Order ' || order_id::text AS label;
SELECT concat('Order ', order_id) AS label;
SELECT concat_ws(', ', city, region, country) AS location
FROM addresses;
With the operator, a null operand makes the concatenated result null. concat ignores null arguments and converts non-string arguments to text. concat_ws means concatenate with separator: it skips null values after the separator argument, but it does not skip empty strings. Thus an empty value can still produce an extra separator. If the distinction matters, use NULLIF to turn empty strings into nulls or handle them explicitly.
For example, concat_ws(' ', NULL, 'Lee') returns Lee, whereas concatenating an empty string and Lee with a space can leave a leading space. Trimming the final result with btrim may help in simple cases, but making each input’s meaning explicit is usually easier to reason about.
Extracting text and locating substrings
substring extracts a range of characters using a starting position and, optionally, a length. Positions are one-based. left and right take a specified number of characters from the beginning or end. These functions return text, so they are useful for display or parsing when the input format is known.
SELECT substring('INV-2026-041' FROM 5 FOR 4); -- 2026
SELECT left('postgresql', 4); -- post
SELECT right('report.csv', 3); -- csv
To find text, use strpos(string, substring) or the SQL expression position(substring IN string). Both return a one-based position, and they return zero when the requested substring is not found. They perform literal searches, not regular-expression matching. A filter such as WHERE strpos(email, '@') > 0 can check for the presence of a character, but it is not a complete email-address validation rule.
split_part(string, delimiter, field_number) returns one delimited field. For example, split_part('north|west|zone3', '|', 2) returns west. It is convenient for consistently formatted strings, but it is not a robust CSV parser: quoted delimiters, escaping, and inconsistent input can make simple splitting incorrect. Prefer storing separate values in separate columns when the pieces have their own meaning.
Trimming, case conversion, and literal replacement
trim, ltrim, and rtrim remove specified characters from both ends, the left end, or the right end. The btrim function is also commonly used for trimming from both ends. Without a character set, trimming removes spaces at the ends; it does not remove spaces from the middle of a value.
SELECT btrim(' ready '); -- ready
SELECT trim(both '-' FROM '---code---'); -- code
SELECT lower('Hello'); -- hello
SELECT upper('Hello'); -- HELLO
SELECT initcap('postgres string functions');
lower, upper, and initcap change letter case. Case conversion and comparisons can depend on the database locale and collation, so results for some languages may differ from assumptions based on English. Lowercasing is not, by itself, a universal solution for case-insensitive comparison or Unicode normalization.
replace(string, from, to) replaces every literal occurrence of one substring with another. translate(string, from, to) works character by character: it maps characters in the first set to corresponding characters in the second, and removes characters that have no corresponding character. For instance, translate('1-800-555', '-', '') removes hyphens. Use replace for a sequence of characters and translate for a simple character mapping.
Regular expressions and pattern matching
When a literal search is not enough, PostgreSQL offers regular-expression operators and functions. The ~ operator tests whether text matches a POSIX regular expression; ~* performs a case-insensitive match. The LIKE operator is simpler and uses % for any sequence of characters and _ for one character. These are different pattern languages: a regular expression such as ^[A-Z]+ is not a LIKE pattern.
regexp_replace replaces matches, and regexp_matches can return matched text. For example:
SELECT regexp_replace('Ref: AB-492', '[^0-9]', '', 'g'); -- 492
SELECT 'user@example.com' ~ '^[^@]+@[^@]+$';
The g flag makes regexp_replace replace all matches; without it, the function replaces only the first match. The second example checks a deliberately simple shape, not whether an address is valid or deliverable. Regular expressions are powerful, but complicated expressions can be hard to maintain and expensive on large text sets. Test edge cases, and avoid claiming that a short pattern fully validates a complex real-world format.
Formatting values and building safe SQL
format builds a formatted string using placeholders. The most useful placeholders include %s for a value converted to text, %I for an SQL identifier, and %L for an SQL literal. For instance, format('Hello, %s', name) creates a greeting. Identifier and literal placeholders matter especially in dynamic SQL, where quoting rules are easy to get wrong.
In PL/pgSQL, dynamic statements should generally pass data values through EXECUTE ... USING rather than inserting them into the command text. Use identifier quoting, such as %I, for a table or column name that must be inserted as SQL syntax; parameters cannot stand in for identifiers. PostgreSQL also provides quote_ident, quote_literal, and quote_nullable for specific quoting tasks. Correct quoting does not replace careful query design, and values should not be concatenated into SQL merely because a string function makes it convenient.
Choosing the right function and avoiding pitfalls
Start by deciding whether you need to manipulate literal text, interpret a pattern, or model structured information. Use literal functions such as replace and strpos when exact text is involved; use regular expressions when the pattern genuinely varies. If a field contains repeated structured data, splitting it on every query may signal that the data belongs in separate columns or rows instead.
- Check null behavior before choosing between
||,concat, andconcat_ws. - Confirm whether a requirement is measured in characters or bytes before choosing a length function.
- Remember that positions in substring and search results are one-based, while a failed
strpossearch returns zero. - Use
LIKEfor simple wildcard matching and regular expressions only when their extra flexibility is useful. - Test case conversion and normalization with the database’s encoding and relevant languages.
Most ordinary string operations are straightforward, but performance depends on the query and data volume. A condition that applies a function to every row may prevent use of a regular index on the original column. For frequent searches, consider an expression index that matches the expression used in the query, or an appropriate text-search or pattern-search index. Index choices depend on the operator and workload; check the query plan with EXPLAIN and measure on representative data rather than assuming every function call has the same cost.
Frequently Asked Questions
What is the difference between concat and concat_ws?concat joins its arguments and ignores null arguments. concat_ws also inserts a chosen separator between values and skips null values after that separator. Neither function automatically treats an empty string as null.
How do I get the first part of a string in PostgreSQL?
Use left(value, count) for the first specified number of characters. To get a delimited field, use split_part(value, delimiter, field_number). Choose based on whether the boundary is a character count or a delimiter.
Does PostgreSQL length count bytes or characters?length and char_length count characters, while octet_length counts bytes in the database encoding. In UTF-8, one character can occupy multiple bytes.
How can I replace every occurrence of text?
Use replace(value, old_text, new_text) for literal replacement. Use regexp_replace when the match is a pattern, and include the g flag when all matching occurrences should be replaced.
Can I use string functions to validate an email address?
Functions and regular expressions can check simple constraints, such as whether a value contains an at-sign, but a homemade pattern may reject valid addresses or accept unusable ones. Use validation appropriate to the application and confirm addresses through a suitable verification process when needed.
Related
- Accounting Trial Balance Worksheet: How It Works
- Google Sheets SPLIT Function: How to Separate Text
- Oracle Split String Example: SQL Methods That Work
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel LAMBDA Function Examples for Reusable FormulasExcel LAMBDA lets you turn a formula into a reusable function: define it with =LAMBDA(parameters, calculation), save it…
- Snowflake MID String Function: Extract Text with SUBSTRSnowflake’s MID function extracts part of a string by position; it is a synonym for SUBSTR (also called…
- Excel Filter Dates by Day of Week: Methods That WorkExcel’s date filter menu does not include a direct day-of-week option, but you can filter weekdays by adding…
- FLOOR Excel Formula vs. ROUNDDOWN: Key DifferencesIn Excel, FLOOR rounds a number down to a chosen multiple, while ROUNDDOWN cuts off digits at a…
- ABS Excel Examples: Formulas and UsesIn Excel, use =ABS(number) to return a number’s distance from zero without its negative sign: for example, =ABS(-25)…
- Excel COUNTIF for Dates Between a RangeTo count dates between two boundaries in Excel, use COUNTIFS, which applies both a start-date and an end-date…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.