PostgreSQL 18 reference · String Functions
PostgreSQL POSITION() Function
Return the 1-based position of a substring inside a string, or 0 if it isn't found. strpos() takes the arguments in the other order.
PostgreSQL 18
Syntax
position(substring IN string) | strpos(string, substring)
Parameters
| Parameter | Description |
|---|---|
substring |
The text to find. |
string |
The input text. |
Return value
An integer (0 when not found).
Examples
SELECT position('@' IN '[email protected]') AS at_sign,
strpos('[email protected]', 'example') AS word;
Output
at_sign | word
---------+------
4 | 5
(1 row)Run this on your computer — see “Get Started” for setup.
SELECT email, substring(email FROM position('@' IN email) + 1) AS domain
FROM customers
WHERE email IS NOT NULL
LIMIT 2;
Output
email | domain ------------------+------------- [email protected] | example.com [email protected] | example.com (2 rows)
Run this on your computer — see “Get Started” for setup.
In other languages
INSTR()· SQLite
Related
SUBSTRING()· Extract part of a string by 1-based position and length, or the part matching a regular expression.SPLIT_PART()· Split a string on a delimiter and return the nth field (1-based). Negative n counts from the end.STARTS_WITH()· Return true if a string starts with a given prefix. The ^@ operator does the same and can use an index.- SQL String Functions · tutorial