The PostgreSQL string functions you need most:
substring(text FROM start FOR length)cuts out part of a string. Positions start at 1.||orconcat()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
leftorrightmeans all but the last (or first) n characters. - With a regular expression after
FROM,substringreturns 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_partreturns an empty string when the field does not exist. A negative field number counts from the end (since PostgreSQL 14).position(sub IN text)andstrpos(text, sub)return the 1-based position, or 0 when not found.replacereplaces every occurrence and is case-sensitive.regexp_replacereplaces 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
| Task | Function | Example result |
|---|---|---|
| Uppercase, lowercase | upper(s), lower(s) | ADA, ada |
| Starts with | starts_with(s, 'pre') or s LIKE 'pre%' | true |
| Repeat, reverse | repeat('ab', 3), reverse('abc') | ababab, cba |
| First match of a regex | substring(s FROM 'pattern') | the matched text |
| All regex matches | regexp_matches(s, 'pattern', 'g') | one row per match |
| Number to text with format | to_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.