POSITION function in Redshift database

In Redshift database, POSITION function is a string function that is used to determine the position of occurrence of a substring within a string.

Syntax:

SELECT POSITION(string, substring);

CHARINDEX function and STRPOS function are synonyms to the POSITION function and they return the same output as POSITION function.

Example:

SELECT POSITION('I watched a movie','watch');
SELECT STRPOS('I watched a movie','watch');
SELECT CHARINDEX('I watched a movie','watch');

All the above 3 queries return the output as ‘3’ as ‘watch’ is in the 3rd position within the ‘I watched a movie’ string.

If the substring cannot be found in the string, ‘0’ is returned as output.

Example:

SELECT POSITION('I watched a movie','game');

Above query returns output as ‘0’.

See also  How to create a table in Redshift database