Menu

PostgreSQL String Functions: substring, concat, split_part

The PostgreSQL string functions you use every day: substring, left and right, || and concat, split_part, position, replace, regexp_replace, trim, lpad and format.

This page includes runnable editors. Edit, run, and see output instantly.

The PostgreSQL string functions you need most:

  • substring(text FROM start FOR length) cuts out part of a string. Positions start at 1.
  • || or concat() joins strings.
  • split_part(text, delimiter, n) takes one field of a delimited string.
  • replace(text, from, to) replaces every occurrence of a substring.

Ada gets Ada Lovelace and the domain example.com. concat_ws skips Lin's missing last name, so her full name is Lin with no trailing space.

Extract part of a string

This returns PostgreS, gre, Post, SQL, Postgre and 0042.

  • substring(... FROM start FOR length) is the SQL standard form; substr(text, start, length) does the same as a plain function. Leave out the length to take the rest of the string.
  • A negative count in left or right means all but the last (or first) n characters.
  • With a regular expression after FROM, substring returns the first match, or the first parenthesized group if the pattern has one.

Join strings: ||, concat and concat_ws

|| returns NULL as soon as one side is NULL. concat treats NULL as an empty string and returns ab. concat_ws puts the separator between the values that are not NULL and returns Ada, London. When one side of || is text, a number on the other side is converted, as in id-42.

To join the values of many rows into one string, use string_agg.

Split, find and replace

The results are example.com, c, 4, 2026/11/17, a#b#c# and {red,green,blue}.

  • split_part returns an empty string when the field does not exist. A negative field number counts from the end (since PostgreSQL 14).
  • position(sub IN text) and strpos(text, sub) return the 1-based position, or 0 when not found.
  • replace replaces every occurrence and is case-sensitive. regexp_replace replaces only the first match unless you pass the 'g' flag; add 'i' to ignore case.
  • regexp_split_to_table(text, pattern) splits into rows instead of an array.

For pattern tests in WHERE (LIKE, ILIKE, ~), see LIKE, ILIKE and regular expressions.

Clean up, pad and format

This returns hello, hi, 00042, Hello World, 5 and Ada has 3 items. ltrim and rtrim trim one side only. lpad and rpad also cut a string that is already longer than the target length.

length counts characters and octet_length counts bytes; they differ for non-ASCII text such as é. format uses %s for a value, %I for a quoted identifier and %L for a quoted literal. That makes it the safe way to build dynamic SQL inside functions.

Quick reference

TaskFunctionExample result
Uppercase, lowercaseupper(s), lower(s)ADA, ada
Starts withstarts_with(s, 'pre') or s LIKE 'pre%'true
Repeat, reverserepeat('ab', 3), reverse('abc')ababab, cba
First match of a regexsubstring(s FROM 'pattern')the matched text
All regex matchesregexp_matches(s, 'pattern', 'g')one row per match
Number to text with formatto_char(1234.5, 'FM9,999.00')1,234.50

The official string functions reference lists the rest.

Frequently Asked Questions

How do I check if a string contains a substring in PostgreSQL?

position('needle' IN haystack) > 0 or strpos(haystack, 'needle') > 0, or in a WHERE clause haystack LIKE '%needle%'. Use ILIKE to ignore case and starts_with(haystack, 'prefix') for a prefix test.

Why does || return NULL in PostgreSQL?

If any operand of || is NULL, the result is NULL. Use concat() or concat_ws(), which skip NULL arguments, or wrap the nullable value in COALESCE(value, '').

Are PostgreSQL strings 1-indexed?

Yes. Positions start at 1, so substring('abc' FROM 1 FOR 1) is a, and position returns 0 when the text is not found.

What is the difference between text and varchar in PostgreSQL?

None in speed or storage. varchar(n) adds a length check; text and varchar without a length have no limit beyond the 1 GB maximum for a value. All string functions accept both.

Coddy programming languages illustration

Learn to code with Coddy

Get started