OCCURS( ) function

Returns a count of the number of times a substring occurs in a specified character value.


OCCURS(string, search_for)



Character. The value to search in.


Character. The character value to search for. Character literal values must be enclosed in quotation marks.




This function compares the value of the search_for parameter to the string parameter on a character-by-character basis, and returns the number of times the search_for parameter is found in the string parameter.

The function is case-sensitive. When it looks for occurrences of the search_for parameter in the string parameter the letter case must match. For example, “United Equipment” is not equivalent to “UNITED EQUIPMENT”. If you are working with data that includes inconsistent letter casing, you can use the UPPER( ) or LOWER( ) functions to convert values to consistent casing before using the OCCURS( ) function.

You can concatenate two or more fields in the string parameter if you want to search in more than one field in a table.


Example Return value





OCCURS(Invoice_Number, "-")

The number of times a hyphen occurs in each value in the Invoice_Number field

Finding invoice numbers with more than one hyphen

If invoice numbers in a table should have only one hyphen, you can use the OCCURS( ) function to create a filter that isolates invoice numbers that have two or more hyphens:

OCCURS(Invoice_Number, "-") > 1

Finding occurrences of one field’s value in another field

You can 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:

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”.

Related reference
UPPER( ) function
LOWER( ) function
ALLTRIM( ) function
AT( ) function

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