OCTETINDEX function - Amazon Redshift
SyntaxArgumentsReturn typeExamples
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).

OCTETINDEX function

The OCTETINDEX function returns the location of a substring within a string as a number of bytes.

Syntax

OCTETINDEX(substring, string)

Arguments

substring

A CHAR string, a VARCHAR string, or an expression that implicitly evaluates to a CHAR or VARCHAR type.

string

A CHAR string, a VARCHAR string, or an expression that implicitly evaluates to a CHAR or VARCHAR type.

Return type

INTEGER

The OCTETINDEX function returns an INTEGER value corresponding to the position of the substring within the string as a number of bytes, where the first character in the string is counted as 1. If the string doesn't contain multibyte characters, the result is equal to the result of the CHARINDEX function. If the string does not contain the substring, the function returns 0. If the substring is empty, the function returns 1.

Examples

To return the postion of the substring q in the string Amazon Redshift, use the following example. This example returns 0 because the substring is not in the string.

SELECT OCTETINDEX('q', 'Amazon Redshift');

+------------+
| octetindex |
+------------+
|          0 |
+------------+

To return the postion of an empty substring in the string Amazon Redshift, use the following example. This example returns 1 because the substring is empty.

SELECT OCTETINDEX('', 'Amazon Redshift');

+------------+
| octetindex |
+------------+
|          1 |
+------------+

To return the postion of the substring Redshift in the string Amazon Redshift, use the following example. This example returns 8 because the substring begins on the eighth byte of the string.

SELECT OCTETINDEX('Redshift', 'Amazon Redshift');

+------------+
| octetindex |
+------------+
|          8 |
+------------+

To return the postion of the substring Redshift in the string Amazon Redshift, use the following example. This example returns 21 because the first six characters of the string are double-byte characters.

SELECT OCTETINDEX('Redshift', 'Άμαζον Amazon Redshift');

+------------+
| octetindex |
+------------+
|         21 |
+------------+