InterSystems IRIS Data Platform 2019.2  /  InterSystems SQL Reference  /  SQL Functions

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 and occurrence count.
string The string expression within which to search for substring. It can be the name of a column, a string literal, or the result of another scalar function, where the underlying data type can be represented as any character type (such as CHAR or VARCHAR2).
substring A substring that is believed to occur within string.
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 1. A start value of 0, the empty string, NULL, or a nonnumeric value cause INSTR to return 0. Specifying start as a negative number causes INSTR to return <null>.
occurrence Optional — A non-zero integer that specifies which occurrence of substring to return when searching from the start position. The default is to return the position of the first occurrence.
INSTR returns the INTEGER data type.
INSTR searches string for substring, and returns the position of the first character of substring. The position is returned as an integer, counting from the beginning of string. If substring is not found, 0 (zero) is returned. INSTR returns NULL if passed a NULL value for either argument.
INSTR supports specifying start as the starting point for substring search. INSTR also support specifying the substring occurrence from that starting point.
INSTR 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 can also be invoked from ObjectScript using the INSTR() method call:
  WRITE $SYSTEM.SQL.INSTR("The broken brown briefcase","br",6,2)
INSTR, CHARINDEX, POSITION, and $FIND 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 returns 11, because “b” is the 11th character in the string:
SELECT INSTR('The quick brown fox','b',1) AS PosInt
The following example returns the length of the last name (surname) for each name in the Sample.Person table. It locates the comma used to separate the last name from the rest of the name field, then subtracts 1 from that position:
INSTR(Name,',',1)-1 AS LNameLen
FROM Sample.Person
The following example returns the position of the first instance of the letter “B” in each name in the Sample.Person table. Because INSTR is case-sensitive, the %SQLUPPER function is used to convert all name values to uppercase before performing the search. Because %SQLUPPER adds a blank space at the beginning of a string, this example subtracts 1 to get the actual letter position. Searches that do not locate the specified string return zero (0); in this example, because of the subtraction of 1, the value displayed for these searches is –1:
INSTR(%SQLUPPER(Name),'B',1)-1 AS BPos
FROM Sample.Person
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-10-18 06:48:42