POSITION()

Procedural SQL (PSQL) StatementsAggregate Functions

`POSITION()`

Finds the position of the first or next occurrence of a substring in a string

Result type

INTEGER

Syntax

  POSITION (substr IN string)
| POSITION (substr, string [, startpos])

Table 1. `POSITION` Function Parameters
Parameter	Description
substr	The substring whose position is to be searched for
string	The string which is to be searched
startpos	The position in string where the search is to start

Returns the (1-based) position of the first occurrence of a substring in a host string.With the optional third argument, the search starts at a given offset, disregarding any matches that may occur earlier in the string.If no match is found, the result is 0.

Note

The optional third argument is only supported in the second syntax (comma syntax).
The empty string is considered a substring of every string.Therefore, if substr is '' (empty string) and string is not NULL, the result is:
- 1 if startpos is not given;
- startpos if startpos lies within string;
- 0 if startpos lies beyond the end of string.
This function fully supports text BLOBs of any size and character set.

Warning

When used on a BLOB, this function may need to load the entire object into memory.This may affect performance if huge BLOBs are involved.

`POSITION` Examples

position ('be' in 'To be or not to be')   -- returns 4
position ('be', 'To be or not to be')     -- returns 4
position ('be', 'To be or not to be', 4)  -- returns 4
position ('be', 'To be or not to be', 8)  -- returns 17
position ('be', 'To be or not to be', 18) -- returns 0
position ('be' in 'Alas, poor Yorick!')   -- returns 0

POSITION()

POSITION()

POSITION Examples

`POSITION()`

`POSITION` Examples