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.

The CHARINDEX function has this syntax:

CHARINDEX function

CHARINDEX(substring, source [ ,start_position ] )
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 type, or a type that can be converted to a character type Expression
start_ position Ordinal position to begin the search in source, where 1 is the first logical character Must be an expression, constant, column, or host variable of a built-in integer type, or that can be converted to an integer Expression
substring 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 CHARINDEX cannot be user-defined data types.

If either source or the substring is NULL, this function returns NULL.

If the optional start_position value is less than 1, or if you omit this argument, none, the search for substring begins at the first logical character in the source, as if you had specified 1 as the starting position.

If no expression matching the substring is found, CHARINDEX returns zero ( 0 ). Otherwise it returns the ordinal position of the first logical character in the first occurrence of substring.

If you specify a start_position greater than 1, any substring that begins before start_position is ignored, and the function returns one of the following values:
  • either the position of the first logical character in the first matching substring whose ordinal position is equal to or greater than start_position,
  • or else zero ( 0 ), if no occurrence of substring in source begins at or follows start_position, or if start_position is greater than the number of logical characters in source.

In locales that support multibyte character sets, the return value is the ordinal value among logical characters in the source. 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.

In databases created with the NLSCASE INSENSITIVE option, if either source or substring is an NCHAR or NVARCHAR data type, the database server ignores variants in letter case in determining whether a given substring of source matches the target substring.

The following function expression returns 9: 
 CHARINDEX('com','www.ibm.com')
In the example above, CHARINDEX begins its search at the default starting position of 1.
The following function expression returns 2: 
 CHARINDEX('w','www.ibm.com',2)
In the example above, because the last argument begins the search at position 2, CHARINDEX ignores two other matching substrings:
  • 'w' in position 1, because the search begins at 2,
  • and 'w' in position 3, because the function returns the position of only the first occurrence of a matching substring.