Substring functions

The built-in SQL substring functions return substrings from character string arguments, or return positional information for operations on substrings.

Substring Functions

Read syntax diagram

Skip visual syntax diagram

Notes:

¹ Informix® extension
² See CHARINDEX function
³ See INSTR function
⁴ See LEFT function
⁵ See RIGHT function
⁶ See SUBSTR function
⁷ See SUBSTRB function
⁸ See SUBSTRING function
⁹ See SUBSTRING_INDEX function

Sections that follow describe the syntax and usage of these substring functions.

CHARINDEX function
The CHARINDEX function searches a character string for the first occurrence of a target substring, where the search begins at a specified or default character position within the source string.
INSTR function
The INSTR function searches a character string for a specified substring, and returns the character position in that string where an occurrence of that a substring ends, based on a count of substring occurrences.
LEFT function
The LEFT function returns a substring consisting of the leftmost N characters from a string argument.
RIGHT function
The RIGHT function returns a substring consisting of the rightmost N characters from a string argument.
SUBSTR function
SUBSTRB function
Returns a substring of a string, beginning at a specified position in the string.
SUBSTRING function
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.

Parent topic: Function Expressions