POSITION

Returns an INTEGER value representing the character location of a specified substring with a string (counting from one).

Behavior type

Immutable

Syntax 1

POSITION ( substring IN string [ USING { CHARACTERS | OCTETS } ] )

Arguments

substring: (CHAR or VARCHAR) is the substring to locate
string: (CHAR or VARCHAR) is the string in which to locate the substring
USING CHARACTERS | OCTETS: Determines whether the position is reported by using characters (the default) or octets.

Syntax 2

POSITION ( substring IN string )

Arguments

substring: (VARBINARY) is the substring to locate
string: (VARBINARY) is the string in which to locate the substring

Notes

When the string and substring are CHAR or VARCHAR, the return value is based on either the character or octet position of the substring.
When the string and substring are VARBINARY, the return value is always based on the octet position of the substring.
The string and substring must be consistent. Do not mix VARBINARY with CHAR or VARCHAR.
POSITION is similar to STRPOS although POSITION allows finding by characters and by octet.
If the string is not found, the return value is zero.

Examples

=> SELECT POSITION('é' IN 'étudiant' USING CHARACTERS);
 position
----------
        1
(1 row)
=> SELECT POSITION('ß' IN 'straße' USING OCTETS);
 positionb
-----------
         5
(1 row)

=> SELECT POSITION('c' IN 'abcd' USING CHARACTERS);
 position
----------
        3
(1 row)

=> SELECT POSITION(VARBINARY '456' IN VARBINARY '123456789');
 position
----------
        4
(1 row)

SELECT POSITION('n' in 'León') as 'default',
       POSITIONB('León', 'n') as 'POSITIONB',
       POSITION('n' in 'León' USING CHARACTERS) as 'pos_chars',
       POSITION('n' in 'León' USING OCTETS) as 'pos_oct',INSTR('León','n'),
       INSTRB('León','n'), REGEXP_INSTR('León','n');
 default | POSITIONB | pos_chars | pos_oct | INSTR | INSTRB | REGEXP_INSTR
---------+-----------+-----------+---------+-------+--------+--------------
       4 |         5 |         4 |       5 |     4 |      5 |            4
(1 row)