STRPOS function - Amazon Redshift
SyntaxArgumentsReturn typeUsage notesExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the blog post .

STRPOS function

Returns the position of a substring within a specified string.

See CHARINDEX function and POSITION function for similar functions.

Syntax

STRPOS(string, substring )

Arguments

string

The first input parameter is the CHAR or VARCHAR string to be searched.

substring

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

Return type

INTEGER

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 |
+--------+

Examples

To show the position of fish within dogfish, use the following example. 

SELECT STRPOS('dogfish', 'fish');

+--------+
| strpos |
+--------+
|      4 |
+--------+

The following example uses data from the SALES table in the TICKIT sample database. For more information, see Sample database.

To return the number of sales transactions with a COMMISSION over 999.00 from the SALES table, use the following example. 

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 |
+--------+-------+