OCCURS( ) function
Syntax
OCCURS(string, search_for)
Parameters
| Name | Type | Description |
|---|---|---|
| string |
character |
The value to search in. You can concatenate two or more fields if you want to search in more than one field in a table: OCCURS(First_Name+Last_Name,"John") |
| search_for |
character |
The value to search for. The search is case-sensitive. |
Output
Numeric.
Examples
Basic examples
Returns 2:
OCCURS("abc/abc/a","ab")
Returns 3:
OCCURS("abc/abc/a","a")
Returns the number of times a hyphen occurs in each value in the Invoice_Number field:
OCCURS(Invoice_Number, "-")
Advanced examples
Finding invoice numbers with more than one hyphen
If invoice numbers in a table should have only one hyphen, use the OCCURS( ) function to create a filter that isolates invoice numbers that have two or more hyphens:
SET FILTER TO OCCURS(Invoice_Number, "-") > 1
Finding occurrences of one field's value in another field
Use OCCURS( ) to find occurrences of one field's value in another field. For example, you could create a filter that isolates records in which Last_Name values occur in the Full_Name field:
SET FILTER TO OCCURS(Full_Name, ALLTRIM(Last_Name)) = 1
Including the ALLTRIM( ) function in the expression removes any leading or trailing spaces from the Last_Name field, ensuring that only text values are compared.
Performing case-sensitive searches
Unlike the FIND( ) function, the OCCURS( ) function is case sensitive, which allows you to perform case-sensitive searches.
The following expression isolates all records that contain the name "UNITED EQUIPMENT", in uppercase, in the Vendor_Name field, while ignoring occurrences of "United Equipment".
SET FILTER TO OCCURS(Vendor_Name, "UNITED EQUIPMENT") > 0
If you want to find all occurrences of "United Equipment" regardless of casing, use the UPPER( ) function to convert the search field values to uppercase:
SET FILTER TO OCCURS(UPPER(Vendor_Name), "UNITED EQUIPMENT") > 0
ACL Scripting Guide 14.1