SUBSTRING_INDEX function

The SUBSTRING_INDEX function searches a character string for a specified delimiter character, and returns a substring of the leading or trailing characters, based on a count of a delimiter that you specify as an argument to the function.

The SUBSTRING_INDEX function has this syntax:


Element	Description	Restrictions	Syntax
source_ string	Expression that evaluates to a character string	Must be an expression, constant, column, or host variable of a built-in character data type, or that can be converted to a character type	Expression
count	Expression that evaluates to a positive or negative integer	Must be an expression, constant, column, or host variable of a built-in integer type, or that can be converted to an integer.	Expression
delimiter	Expression that evaluates to a character string	Must be an expression, constant, column, or host variable of a built-in character data type, or that can be converted to a character type	Expression

Arguments to SUBSTRING_INDEX cannot be user-defined data types.

This function returns NULL in each of the following cases:

source_string is NULL
delimiter is NULL
count = zero (0 ).

If the search finds fewer than count delimiters in the source_string, the return value is the entire source_string.

The return value has the same data type as the source_string.

For source_string, the sign of count determines whether the returned value is a substring of the leading characters or of the trailing characters in source_string:

The last character in the returned substring immediately precedes the Nth occurrence of that delimiter in a substring of leading characters, for N = count.
For example, the function expression
```
SUBSTRING_INDEX("www.ibm.com", ".", 2)  
```
returns the leading characters www.ibm because count > 0.
The first character in the returned substring immediately precedes the Nth occurrence of that delimiter in a substring of trailing characters, for N = count < 0.
For example, the function expression
```
SUBSTRING_INDEX("www.ibm.com", ".", -2)  
```
returns the trailing characters ibm.com because count < 0.

The examples above apply to left-to-right locales, such as the default U.S. English locale, in which a negative value of count causes this function to return a substring of trailing characters from the source_string, and a positive value of count causes this function to return a substring of leading characters from the source_string,

In locales for right-to-left languages such as Arabic, Farsi, or Hebrew, the opposite is true. This function returns a substring of leading characters from the source_string if count has a negative value, and returns a substring of trailing characters if count has a positive value.

In locales that support multibyte character sets, the return value is the ordinal value among logical characters in the source_string. In single-byte locales, such as the default locale, the return value is equivalent to the byte position, where the first byte is in position 1.