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’.