The OCTETINDEX function returns the location of a substring within a string as a number of bytes.
Syntax
OCTETINDEX(substring, string)
Arguments
- substring
-
A
CHARstring, aVARCHARstring, or an expression that implicitly evaluates to aCHARorVARCHARtype. - string
-
A
CHARstring, aVARCHARstring, or an expression that implicitly evaluates to aCHARorVARCHARtype.
Return type
- INTEGER
-
The OCTETINDEX function returns an
INTEGERvalue 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 returns0. If the substring is empty, the function returns1.
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 |
+------------+