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 } ] )

Parameters

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 )

Parameters

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)