FIND( ) function

Returns a logical value indicating whether the specified string is present in a particular field, or anywhere in an entire record.

Syntax

FIND(string <,field_to_search_in>)

Parameters

string

Character. The character string to search for. The string must be enclosed in quotation marks. The search is not case-sensitive.

field_to_search_in

Optional. Character. Specifies the field, or variable, to search in. If this parameter is omitted the entire record is searched.

Output

Logical. Returns T (true) if the specified string value is found, and F (false) otherwise.

Remarks

Note

The FIND( ) function and the FIND command are two separate ACL features with significant differences.

The FIND( ) function tests for the presence of the specified string in a field, two or more fields, or an entire record. The string value can be exactly matched or it can be contained within a longer string. Leading spaces in fields do not affect the search unless you include one or more leading spaces in the string value.

If the optional field_to_search_in parameter is not specified, the entire record is searched, including any undefined portion of the record. Field boundaries are ignored when the entire record is searched, and trailing spaces in fields are treated as characters.

You can concatenate two or more fields in the field_to_search_in parameter if you want to search in a subset of the fields in a table. For example, FIND("New York", City+City_2)searches both the City and the City_2 fields for the string “New York”. The concatenated fields are treated like a single field that includes leading and trailing spaces from the individual fields, unless you use the ALLTRIM( ) function to remove spaces. You can also build an expression that searches each field individually – for example, FIND("New York", City) OR FIND("New York", City_2). If the string value includes a leading space, search results from these two approaches can differ.

The FIND( ) function is not case-sensitive, and finds both ASCII and EBCDIC characters. The function is not affected by the Exact Character Comparisons option (SET EXACT ON/OFF).

To search in a related field you must specify the fully qualified name of the field (that is, table.field name) in the field_to_search_in parameter. For example: FIND("New York", Vendor.Vendor_City)

It is possible to use the FIND( ) function to search datetime or numeric data at the record level. The numeric or datetime string value must be enclosed in quotation marks, and needs to exactly match the source data formatting rather than the formatting in the view. Specifying the field_to_search_in parameter is not supported. Using the FIND( ) function this way is not recommended because it can be difficult to do successfully.

Examples

Example Return value

FIND("New York")

Returns ‘T’ for all records that contain the string “New York” in any field, across any field boundaries, and in any undefined portion of the record. Returns ‘F’ otherwise.

FIND("New York", City)

Returns ‘T’ for all records that contain the string “New York” in the City field. Returns ‘F’ otherwise.

FIND("Ne", City)

Returns ‘T’ for all records that contain the string “Ne” in the City field. Returns ‘F’ otherwise.

FIND(" New York", City)

Returns ‘T’ for all records that contain the string “New York” preceded by one or more spaces in the City field. Returns ‘F’ otherwise.

FIND("New York", City+City_2)

Returns ‘T’ for all records that contain the string “New York” in either the City or the City_2 fields. Returns ‘F’ otherwise.

FIND("New York", City) OR FIND("New York", City_2)

Returns ‘T’ for all records that contain the string “New York” in either the City or the City_2 fields. Returns ‘F’ otherwise.

FIND(ALLTRIM(Last_Name),Last_Name_2)

Returns ‘T’ for all records that have a value in the Last_Name_2 field that matches or contains the value in the Last_Name field. Returns ‘F’ otherwise.

FIND(v_search_term, Description)

Returns ‘T’ for all records that contain the value in the v_search_term variable in the Description field. Returns ‘F’ otherwise.

Related reference
MATCH( ) function
ALLTRIM( ) function


(C) 2015 ACL Services Ltd. All Rights Reserved.