STRPOS function - Amazon Redshift

STRPOS function

Returns the position of a substring within a specified string.

Synonym of CHARINDEX function and POSITION function.


STRPOS(string, substring )



The first input parameter is the string to be searched.


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:

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


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

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:

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)