CHARINDEX (SQL)

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

Synopsis

CHARINDEX(substring,string[,start])

Description

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 InterSystems SQL as NULL.

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

CHARINDEX cannot use a %Stream.GlobalCharacter field for either the string or substring argument. Attempting to do so generates an SQLCODE -37 error. You can use the SUBSTRING function to take a %Stream.GlobalCharacter field and return a %String data type value for use by CHARINDEX.

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

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 supports 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

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

Arguments

substring

A substring to match within string.

string

A string expression that is the target for the substring search.

start

An optional argument that denotes 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 returns the INTEGER data type.

Examples

The following example searches a nucleotide sequence for the first occurrence of the substring TTAGGG. It returns 7, the character position of this substring within the string:

SELECT CHARINDEX('TTAGGG','TTAGTCTTAGGGACATTAGGG')

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

SELECT Name
FROM Sample.Person
WHERE CHARINDEX('Fred',Name)>0

The following example uses SUBSTRING to allow CHARINDEX to search the first 1000 characters of a %Stream.GlobalCharacter field containing DNA nucleotide sequences for the first occurrence of the substring TTAGGG:

SELECT CHARINDEX('TTAGGG',SUBSTRING(DNASeq,1,1000)) FROM Sample.DNASequences

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

SELECT CHARINDEX('Re','Reduce, Reuse, Recycle',10)

It returns 16.

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

SELECT CHARINDEX('Re','Reduce, Reuse, Recycle',99)

It returns 0.

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

SELECT CHARINDEX('','Fred Astare'),
       CHARINDEX('A',''),
       CHARINDEX('','')

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:

SELECT CHARINDEX(NULL,'Fred Astare'),
       CHARINDEX('A',NULL),
       CHARINDEX(NULL,NULL)