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:

SUBSTRING_INDEX function

SUBSTRING_INDEX(source_string ,delimiter,count)
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 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.