Product Documentation

Appearance

STRPOS ​

Return the numeric position of the specified substring within a character string. Optionally, specify a starting position and an occurrence number.

STRPOS(string, substring, [, position [, occurrence] ])

INSTR is an alias for STRPOS.

Tip: The two-argument version of this function is equivalent to POSITION(substring IN string), but the order of the arguments is reversed.

Parameters ​

string

Specify a character string or a string expression, such as a column name, that will be searched.

substring

Specify a substring to search for within the first string. If you specify an empty string, the function returns 1.

position

Optionally, specify a starting position in substring. This argument must be an integer data type. The purpose of this argument is to provide a way for the evaluation of the function to be based on only the part of the string that starts at the specified position. However, regardless of the position value you specify, the function result always counts from the beginning of string.

Position 0 is treated the same as 1.

occurrence

Optionally, specify which occurrence of substring to evaluate, assuming that substring occurs multiple times within string. This value must be an integer greater than 0.

Examples ​

For example, find team names that contain City and return the position in the string where City begins. Return 0 if City is not found. (If name is null, return null.)

premdb=# select name, strpos(name, 'City') from team;
         name           | strpos 
-------------------------+--------
 Arsenal                 |      0
 Aston Villa             |      0
 Barnsley                |      0
 Birmingham City         |     12
 Blackburn Rovers        |      0
 Blackpool               |      0
 Bolton Wanderers        |      0
 Bournemouth             |      0
 Bradford City           |     10
 Burnley                 |      0
 Cardiff City            |      9
...

Return the position of the third instance of the string er in team names. Start searching from position 1 in each name. Return only those rows where er appears for a third time.

premdb=# select name, strpos(name, 'er', 1, 3) instring from team where instring>0;
         name           | instring 
-------------------------+----------
 Wolverhampton Wanderers |       21
(1 row)

Adjust the previous query to return the position of the second instance of the string er.

premdb=# select name, strpos(name, 'er', 1, 2) instring from team where instring>0;
         name           | instring 
-------------------------+----------
 Bolton Wanderers        |       14
 Wolverhampton Wanderers |       19
(2 rows)

Adjust the previous query to return the position of the second instance of the string er, starting from position 15 in each team name.

premdb=# select name, strpos(name, 'er', 15, 2) instring from team where instring>0;
         name           | instring 
-------------------------+----------
 Wolverhampton Wanderers |       21
(1 row)

Parent topic:String Functions