Skip to main content

$FIND (SQL)

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

Synopsis

$FIND(string,substring[,start])

Description

$FIND returns an integer specifying the end position of a substring within a string. $FIND searches string for substring. If substring is found, $FIND returns the integer position of the first character following substring. If substring is not found, $FIND returns a value of 0.

You can include the start option to specify a starting position for the search. If start is greater than the number of characters in string, $FIND returns a value of 0. If start is omitted, string position 1 is the default. If start is zero, a negative number, or a nonnumeric string, position 1 is the default.

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

$FIND, POSITION, CHARINDEX, and INSTR

$FIND, POSITION, CHARINDEX, and INSTR all search a string for a specified substring and return an integer position corresponding to the first match. $FIND returns the integer position of the first character after the end of the matching substring. CHARINDEX, POSITION, and INSTR return the integer position of the first character 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.

Arguments

string

The target string that is to be searched. It can be a variable name, a numeric value, a string literal, or any valid expression.

substring

The substring that is to be searched for. It can be a variable name, a numeric value, a string literal, or any valid expression.

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, or a nonnumeric value is treated as 0. Specifying start as NULL causes $FIND to return <null>.

$FIND returns the SMALLINT data type.

Examples

In the following example, string contains the string “ABCDEFG” and substring contains the string “BCD”. The $FIND function returns the value 5, indicating the position of the character (“E”) that follows “BCD”:

SELECT $FIND('ABCDEG','BCD') AS SubPoint

The following example searches the numeric 987654321 for the number 7. It returns 4, the position following the substring:

SELECT $FIND(987654321,7) AS SubPoint

The following example returns 3, the position of the character that follow the first instance of the substring “AA”:

SELECT $FIND('AAAAAA','AA') AS SubPoint

In the following example, $FIND searches for a substring that is not in the string. It returns zero (0):

SELECT $FIND('AABBCCDD','AC') AS SubPoint

In the following example, $FIND begins its search with the seventh character. This example returns 14, the position of the character that follows the next occurrence of “R”:

SELECT $FIND('EVERGREEN FOREST','R',7) AS SubPoint

In the following example, $FIND begins its search after the last character in string. It returns zero (0):

SELECT $FIND('ABCDEFG','G',10) AS SubPoint

The following example shows that a start less than 1 is treated as 1:

SELECT 
$FIND("ABCDEFG","F"),
$FIND("ABCDEFG","F",1),
$FIND("ABCDEFG","F",0),
$FIND("ABCDEFG","F",-35)

The following example uses $FIND to search a string containing the Unicode character for pi, $CHAR(960). The first $FIND returns 5, the character following pi. The second $FIND also returns 5; it begins its search at character 4, which happens to be pi, the character sought. The third $FIND begins its search at character 5; it returns 13, the position following the next occurrence of pi. Note that position 13 is returned, even though position 12 is the last character in the string:

SELECT 
$FIND("QT "_$CHAR(960)_" HONEY "_$CHAR(960),$CHAR(960)),
$FIND("QT "_$CHAR(960)_" HONEY "_$CHAR(960),$CHAR(960),4),
$FIND("QT "_$CHAR(960)_" HONEY "_$CHAR(960),$CHAR(960),5)

See Also

FeedbackOpens in a new tab