Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

STRPOS Function

Returns the position of a substring within a specified string.

Synonym of CHARINDEX Function and POSITION Function.

Syntax

Copy
STRPOS(string, substring )

Arguments

string

The first input parameter is the string to be searched.

substring

The second parameter is the substring to search for within the string.

Return Type

The STRPOS function returns an integer corresponding to the position of the substring (one-based, not zero-based). The position is based on the number of characters, not bytes, so that multi-byte characters are counted as single characters.

Usage Notes

STRPOS returns 0 if the substring is not found within the string:

Copy
select strpos('dogfish', 'fist'); strpos -------- 0 (1 row)

Examples

The following example shows the position of the string fish within the word dogfish:

Copy
select strpos('dogfish', 'fish'); strpos -------- 4 (1 row)

The following example returns the number of sales transactions with a COMMISSION over 999.00 from the SALES table:

Copy
select distinct strpos(commission, '.'), count (strpos(commission, '.')) from sales where strpos(commission, '.') > 4 group by strpos(commission, '.') order by 1, 2; strpos | count --------+------- 5 | 629 (1 row)