Skip to main content

This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Previous sectionNext section


A string function that returns the position of a substring within a string, with optional search start point.




Argument Description
substring A substring to match within string.
string A string expression that is the target for the substring search.
start Optional — The starting point for substring search, specified as a positive integer. A character count from the beginning of string, counting from 1. To search from the beginning of string, omit this argument or specify a start of 0 or 1. A negative number, the empty string, NULL, or a nonnumeric value is treated as 0.


CHARINDEX searches a string for a substring. If a match is found, it returns the starting position of the first matching substring, counting from 1. If the substring cannot be found, CHARINDEX returns 0.

The empty string is a string value. You can, therefore, use the empty string for either string argument value. The start argument treats an empty string value as 0. However, note that the ObjectScript empty string is passed to Caché SQL as NULL.

NULL is not a string value in Caché SQL. For this reason, specifying NULL for either CHARINDEX string argument returns NULL.

CHARINDEX is case-sensitive. Use one of the case-conversion functions to locate both uppercase and lowercase instances of a letter or character string.

This function provides compatibility with Transact-SQL implementations.


CHARINDEX, POSITION, $FIND, and INSTR all search a string for a specified substring and return an integer position corresponding to the first match. CHARINDEX, POSITION, and INSTR return the integer position of the first character of the matching substring. $FIND returns the integer position of the first character after the end of the matching substring. CHARINDEX, $FIND, and INSTR support specifying a starting point for substring search. INSTR also support specifying the substring occurrence from that starting point.

The following example demonstrates these four functions, specifying all optional arguments. Note that the positions of string and substring differ in these functions:

SELECT POSITION('br' IN 'The broken brown briefcase') AS Position,
       CHARINDEX('br','The broken brown briefcase',6) AS Charindex,
       $FIND('The broken brown briefcase','br',6) AS Find,
       INSTR('The broken brown briefcase','br',6,2) AS Inst
Copy code to clipboard

For a list of functions that search for a substring, refer to String Manipulation.


The following example searches for the substring KONG. It returns 6, the character position of this substring within the string:

Copy code to clipboard

The following example searches for all Name field values that contain the substring 'Fred':

FROM Sample.Person
Copy code to clipboard

The following example matches a substring after the first 10 characters:

SELECT CHARINDEX('Re','Reduce, Reuse, Recycle',10)
Copy code to clipboard

it returns 16.

The following example specifies a start location beyond the length of the string:

SELECT CHARINDEX('Re','Reduce, Reuse, Recycle',99)
Copy code to clipboard

it returns 0.

The following example shows that CHARINDEX handles the empty string ('') just like any other string value:

Copy code to clipboard

In the above example, the first and second CHARINDEX functions return 0 (no match). The third returns 1, because the empty string matches the empty string at position 1.

The following example shows that CHARINDEX does not treat NULL as a string value. Specifying NULL for either string always returns NULL:

Copy code to clipboard

See Also