IndexOf Function
The IndexOf() function returns the zero-based index of the first occurrence of a specified Unicode character or string within string instance. It can take from 1 to 4 arguments with 2 required and 2 optional.
The function returns -1 if the character or string is not found.
As default it works as case insensitive and supports .net StringComparison enums to provide maximum flexibility. Since it returns
index, the value for first character is zero (0). You can also use binary and NoCase as fourth parameter for case sensitive and case insensitive search.
The syntax for IndexOf function is IndexOf( String , String, [ Start Index ] , [ StringComparison as string ] ).
The [ StringComparison as string ] can have one of the following values.
| Argument Value |
Comparison Type |
| CurrentCulture |
Compare strings using culture-sensitive sort rules and the current culture. |
| CurrentCultureIgnoreCase |
Compare strings using culture-sensitive sort rules, the current culture, and ignoring the case of the strings being compared. |
| InvariantCulture |
Compare strings using culture-sensitive sort rules and the invariant culture. |
| InvariantCultureIgnoreCase |
Compare strings using culture-sensitive sort rules, the invariant culture, and ignoring the case of the strings being compared. |
| Ordinal |
Compare strings using ordinal (binary) sort rules. |
| OrdinalIgnoreCase |
Compare strings using ordinal (binary) sort rules and ignoring the case of the strings being compared. |
| Binary |
Compare strings using ordinal (binary) sort rules. |
| NoCase |
Compare strings using ordinal (binary) sort rules and ignoring the case of the strings being compared. |
SELECT IndexOf ('Database', 'D') /* returns 0 */ ;
SELECT IndexOf ('Database', 'a') /* returns 1 */ ;
SELECT IndexOf ('Database', 'a', 2) /* returns 3 as start index is defined at 2 */ ;
SELECT IndexOf ('Database', 'a', 2, 'Ordinal') /* returns 3 as start index is defined at 2 and StringComparison is Ordinal */ ;
SELECT IndexOf ('Database', 'A', 2, 'Ordinal') /* returns -1 as no capital A is found due to Ordinal which is case sensitive */ ;
SELECT IndexOf ('Database', 's', 0, 'CurrentCultureIgnoreCase') ; /* returns 6 as position of 's' using CurrentCultureIgnoreCase which is case insensitive */
This Function is useful to find the first occurrence of a string within another string using different values of the StringComparison enumeration.
Character sets may include ignorable characters, which are characters that are not considered when performing a linguistic or culture-sensitive comparison, they will be ignored or included see IndexOf function in .NET.