InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference

InterSystems SQL Reference
Previous section           Next section
InterSystems: The power behind what matters   

A string function that returns the position of a substring within a string, with optional search start point.
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 returns the INTEGER data type.
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 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
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:
The following example searches for all Name field values that contain the substring 'Fred':
FROM Sample.Person
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:
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:
See Also

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-08-23 06:48:00