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 returns1
. - 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 theposition
value you specify, the function result always counts from the beginning ofstring
.Position
0
is treated the same as1
. - occurrence
- Optionally, specify which occurrence of
substring
to evaluate, assuming thatsubstring
occurs multiple times withinstring
. This value must be an integer greater than0
.
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)