Find
Searches a string, a category list, or an array, for a specified substring, subcategory list, or subarray, and if it is found, returns its start position.
Syntax
Find(Val, Key [, Skip [, Reverse [, IgnoreCase]]])
Parameters
Val
Type: None
Text value, categorical value, or array to operate on.
Key
Type: None
Value to search for in Val.
Skip
Type: Long
Optional. The number of positions to skip in Val before starting the search. If Reverse is True, Skip is the number of positions to skip from the end of Val. If Reverse is omitted or False, Skip is the number of positions to skip from the start of Val. Treated as 0 if omitted or is < 0.
Reverse
Type: Boolean
Optional. If True, Val is searched backwards. If Reverse is omitted or False, Val is searched forwards.
IgnoreCase
Type: Boolean
Optional. If False, the comparison is case-sensitive. If IgnoreCase is omitted or True, the comparison is case-insensitive.
(return)
Type: Long
Position of Key (if found) in Val, relative to the start of Val; -1 otherwise.
Remarks
If Val is Text, Key is converted to text if necessary and is searched for as a substring of Val. Unless IgnoreCase is False, the comparison is case-insensitive.
If Val is Categorical or an array (which can contain values of any type), Key can be a long, double, text, date, boolean, or categorical value, or an array. When comparing Key (or the items in Key) with the items in Val, the normal conversion and comparison rules for variant types are used if necessary. Unless IgnoreCase is False, the comparison of text items (or items converted to text) is case-insensitive. If either item is Null, they are equal only if both are Null. If Key is a categorical value or an array, it is found only if its items occur as a sequence of consecutive items in Val and in the same order.
If either Val or Key is NULL, the return value is -1 (not found). If Val is not Text, Categorical, or an array, an error occurs.
How you specify a text string as an argument depends on whether you are using the function in an SQL query or in mrScriptBasic or mrScriptMetadata. In an SQL query, enclose text strings in ' ' (single quotation marks). In mrScriptBasic or mrScriptMetadata, enclose text strings in " " (double quotation marks).
Examples
|
Function call
|
Val
|
Length
|
Result
|
Notes
|
|
Find(Val, Key)
|
24 St John's Road, Barnes
|
barnes
|
19
|
A forward search of a text value, starting from the beginning of the address. The comparison is case-insensitive.
|
|
Find(Val, Key, , True)
|
3 Glasgow Hill, Glasgow
|
glasgow
|
16
|
A backward search of a text value, starting from the end of the address. The comparison is case-insensitive.
|
|
Find(Val, Key)
|
{3,7,8,1,9,10,5,4}
|
5
|
6
|
A forward search of a categorical value, starting from the first category.
|
|
Find(Val, Key, 7)
|
Val: {3,7,8,1,9,10,5,4}
|
5
|
-1
|
A forward search of a categorical value, skipping the first 7 categories.
|
|
Find(Val, Key, , , False)
|
24 St John's Road, Barnes
|
barnes
|
-1
|
A forward search of a text value, starting from the beginning of the address. The comparison is case-sensitive.
|
|
Find(Val, Key)
|
Split("The quick brown fox")
|
Split("brown fox")
|
2
|
A forward search for an array within another array. See Split for more information. |
Examples using text variables
These examples use the name and address variables, which are text variables that store respondents' names and addresses, respectively. These examples show how you would use the Find function in mrScriptBasic or mrScriptMetadata. In SQL queries, enclose text strings in ' ' (single quotation marks) instead of " " (double quotation marks).
The following example selects any respondent whose address contains “London” in upper case, lower case, or a mixture of both cases. Find returns -1 when the search string has not been found. In this example the <> operator is used to test that the return value is not -1, which means that “London” exists somewhere in the text being searched.
address.Find("London") <> -1
Find lets you search for a string in a particular position. This is useful when the position of the search string in the text you are searching is important. For example, the following selects respondents whose names begin with the string “Brian”:
Find(name, "Brian") = 0
Examples using categorical variables
These examples use the remember multiple response variable.
This example selects all respondents who chose the Dinosaurs category regardless of its position in the list of categories chosen:
remember.Find({dinosaurs}) <> -1
The following example selects respondents for whom the Dinosaurs category is the first category in the list of their responses:
remember.Find({dinosaurs}) = 0
See
Example SQL queries for an example on using Find in SQL queries.
See also