SEARCH( )
Finds the location of a substring in a string. The substring can contain wildcard characters.
Syntax
SEARCH(pattern, str)
SEARCH(pattern, str, startPosition)
Arguments
pattern
The string pattern to search for. You must enclose the pattern in double quotation marks (" "). You can use the following special characters in a pattern:
An asterisk ( * ) matches zero or more characters, including spaces. For example, t*n matches tn, tin, and teen.
A question mark (?) matches exactly one character. For example, t?n matches tan, ten, tin, and ton. It does not match teen or tn.
str
The string to search.
startPosition
The position in str where the search starts.
Returns
The numerical position of the string pattern in the string. The first character of a string starts at 1. If the substring is not found, SEARCH( ) returns 0.
Example
The following example searches for the string pattern, S*A, in each product code. If the product name is KBS5412A, SEARCH( ) returns 3.
SEARCH("S*A", [ProductCode])
The following example uses SEARCH( ) in conjunction with the LEFT( ) function to display the characters preceding the first space character in a product name. The LEFT( ) function extracts a substring of a specified length, starting from the first character. In this example, the length of the substring to display is equal to the numerical position of the space character.
LEFT([ProductName], SEARCH(" ", [ProductName]))
If the product name is 1969 Ford Falcon, LEFT( ) returns 1969.