Examples of search expressions

Examples of search expressions appear below, grouped by ACL function. The expressions use a function to create a filter that isolates all records that match the filter criteria. You enter the expression in the Filter text box at the top of the View tab. You can also use the expressions with ACL operations to restrict which records are processed.

For information about the effects of leading spaces, case sensitivity, partial matching, and exact character comparisons on each function, see A comparison of ACL search methods. For detailed information about each function, see the ACL Language Reference.

Table 1. Examples of search expressions

Function/Expression

Result

FIND( ) function

FIND("United Equipment")

Isolates all records that contain the name “United Equipment” anywhere in the record.

FIND("United Equipment", Vendor_Name)

Isolates all records that contain the name “United Equipment” in the Vendor_Name field.

FIND("Equip")

Isolates all records that contain the string “Equip” anywhere in the record.

MATCH( ) function

(Character examples assume that the Exact Character Comparisons option is off, except where noted.)

MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")

Isolates all records in which the value in the Vendor_City field exactly matches, or begins with, “Phoenix”, “Austin”, or “Los Angeles”.

NOT MATCH(Vendor_City, "Phoenix", "Austin", "Los Angeles")

Isolates all records in which the value in the Vendor_City field does not exactly match, or begin with, “Phoenix”, “Austin”, or “Los Angeles”.

MATCH("Phoenix", Vendor_City, City, City_2)

Isolates all records in which the value in the Vendor_City, City, or City_2 fields exactly matches, or begins with, “Phoenix”.

MATCH(Product_Code, "A", "D", "F")

Isolates all records that have product codes “A”, “D”, or “F”, or product codes beginning with “A”, “D”, or “F”, in the Product_Code field.

MATCH(Product_Code, "A", "D", "F")

Isolates all records that have one-character product codes “A”, “D”, or “F” in the Product_Code field.

The Exact Character Comparisons option must be on.

MATCH(Vendor_Address, Employee_Address)

Isolates all records with identical vendor and employee addresses.

You may need to use additional functions to standardize the format of vendor and employee addresses.

MATCH(Invoice_Date, `20140930`, `20141030`)

Isolates all records with an invoice date of 30 Sep 2014 or 30 Oct 2014.

NOT MATCH(Inventory_Value_at_Cost, Cost_x_Quantity)

Isolates all records in an Inventory table with different amounts in the Inventory_Value_at_Cost field and the computed Cost_x_Quantity field.

MAP( ) function

MAP(Invoice_Number, "XX99999")

Isolates all records with invoice numbers that consist of, or that start with, two letters followed by five numbers.

MAP(Invoice_Number, "AB12345")

Isolates all records with invoice numbers that are exactly “AB12345”, or that start with “AB12345”.

MAP(Invoice_Number, "AB99999")

Isolates all records with invoice numbers that consist of, or that start with, “AB” followed by five numbers.

NOT MAP(SSN, "999-99-9999")

Isolates all records that do not match the standard format of social security numbers in the SSN field.

OCCURS( ) function

OCCURS(Invoice_Number, "-") > 1

Isolates all records in which the invoice number contains 2 or more hyphens.

OCCURS(Full_Name, ALLTRIM(Last_Name))=1

Isolates all records in which the value in the Last_Name field appears in the Full_Name field.

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.

OCCURS(Vendor_Name, "UNITED EQUIPMENT") > 0

Isolates all records that contain the name “UNITED EQUIPMENT”, in uppercase, in the Vendor_Name field.

Unlike the FIND( ) function, the OCCURS( ) function is case sensitive.

AT( ) function

AT(2, "-", Invoice_Number) > 10

Isolates all records in which the invoice number contains 2 or more hyphens, and the second hyphen occurs after the tenth character in the string.

BETWEEN( ) function

BETWEEN(Last_Name, "C", "K")

Isolates all records in which the value in the Last_Name field begins with one of the letters from “C” to “K”, inclusive.

The Exact Character Comparisons option must be off.

BETWEEN(Invoice_Date, `20140930`, `20141030`)

Isolates all records with an invoice date from 30 Sep 2014 to 30 Oct 2014, inclusive.

NOT BETWEEN(Invoice_Date, `20140930`, `20141030`)

Isolates all records with an invoice date that does not fall between 30 Sep 2014 and 30 Oct 2014, inclusive.

BETWEEN(Invoice_Amount, 1000, 5000)

Isolates all records with an invoice amount from $1000 to $5000, inclusive.

REGEXFIND( ) function

REGEXFIND(Vendor_City, "Phoenix|Austin|Los Angeles")

Isolates all records in which the value in the Vendor_City field contains “Phoenix”, “Austin”, or “Los Angeles”.

REGEXFIND(Product_Code, "\b\d{3}-[a-zA-Z]{6}\b")

Isolates all records with a product code that starts with 3 numbers, followed by a hyphen and 6 letters.

REGEXFIND(Product_Code, "\b\d{3,}-[a-zA-Z]{6}")

Isolates all records with a product code that starts with 3 or more numbers, followed by a hyphen and 6 or more letters.

ISFUZZYDUP( ) and LEVDIST( ) functions

ISFUZZYDUP(Last_Name, "Braun", 2)

Isolates all records with the name “Braun”, or fuzzy duplicates of the name “Braun”, in the Last_Name field.

The Levenshtein Distance (degree of fuzziness), set to 2 in this example, can be increased or decreased.

LEVDIST(TRIM(Last_Name), "Braun") < 3

Isolates all records with the name “Braun”, or fuzzy duplicates of the name “Braun”, in the Last_Name field.

The Levenshtein Distance (degree of fuzziness), set to < 3 in this example, can be increased or decreased.

Including the TRIM( ) function in the expression removes any trailing spaces from the last name field, ensuring that only text values are compared.

ISBLANK( ) function

ISBLANK(First_Name)

Isolates all records with a blank First_Name field.

Related concepts
About searching data
Related tasks
Searching data to isolate all matching records
Searching data for the first matching record
Related reference
A comparison of ACL search methods


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