About searching data

There are two different methods for searching data in ACL tables:

These two methods include a number of different capabilities and restrictions, explained in the sections that follow. Depending on the goal of a particular search, a restriction associated with a function or operation, such as case sensitivity, may be desirable.

Indexing a table is not required when you use a function to search. Indexing, in ascending order, is required before using the Find Literal or Seek Expression operations.

Quick or simple searches

The search methods explained in the sections that follow provide a number of advanced options. For more straightforward searches, such as exactly matching, or partially matching, a particular value, you may find using the quick searching, quick filtering, or standard filtering features are an easier way to achieve your goal. For more information, see About quick searching, About quick filters, and About global filters (view filters).

Searching to isolate all matching records

You can use any of the functions in Table 1 to create a filter that isolates all matching records in a table. All the functions can be used with character fields. Only the MATCH( ) and BETWEEN( ) functions can be used with datetime or numeric fields. It is possible to use the FIND( ) function to search datetime or numeric data at the record level. However, using the FIND( ) function this way is not recommended because it can be difficult to do successfully.

For a comparison of the features of ACL search functions, see A comparison of ACL search methods. For step-by-step instructions on using a function to search data, see Searching data to isolate all matching records. For detailed information about each function, see the ACL Language Reference.

Table 1. ACL search functions

Function

Description

FIND( )

The search function with the fewest restrictions. Not case-sensitive. Allows searching entire records in addition to searching an individual field or fields.

MATCH( )

A versatile search function that allows you to search a field for multiple different search terms simultaneously, or search multiple fields for the same search term. Also allows you to find matching values in two fields.

MAP( )

Allows you to search using wildcard characters, literal characters, or a mix of both.

OCCURS( )

Allows you to search for one or multiple occurrences of a substring in a character field.

AT( )

Allows you to search for a substring, or a subsequent occurrence of the substring, in a character field, and specify the starting byte position of the target substring.

BETWEEN( )

Allows you to search for values that fall within a range.

REGEXFIND( )

The most powerful and flexible search function. Allows you to search using regular expressions that combine literal characters and metacharacters. Can be more complicated to use than other search functions.

ISFUZZYDUP( )

Allows you to search for nearly identical values (fuzzy duplicates), as well as identical values. Not case-sensitive.

LEVDIST( )

For the purposes of searching, the same as ISFUZZYDUP( ), but case-sensitive by default.

ISBLANK( )

Allows you to search for blank values in a character field

Searching for the first matching record

You can use any of the search operations (commands) in Table 2, accessed from the ACL main menu, to search a table for the first record matching the search criteria. All the operations can be used with character fields. Only the Locate If operation can be used with datetime or numeric fields. When used to search character fields, all operations are case-sensitive. You can use the Locate Record operation to search by record number.

The Locate If operation searches a table sequentially and therefore is slower than the Find Literal or Seek Expression operations, which search indexed tables. However, the Locate If operation does not require the time spent to index a table. It also maintains the original order of the records in a table, which depending on the nature of your analysis may be desirable.

Each of the operations has an equivalent ACL command that can be run in the command line, or used in an ACL script.

For a comparison of the features of ACL search operations, see A comparison of ACL search methods. For step-by-step instructions on using an operation to search data, see Searching data for the first matching record.

Table 2. ACL search operations

Operation

Equivalent command

Description

Find Literal

FIND

Locates the first occurrence of a character literal (for example, New York) in a character field indexed in ascending order.

Seek Expression

SEEK

Locates the first occurrence of a character literal (for example, “New York”), or a character expression (for example, v_city), in a character field indexed in ascending order.

Locate If

LOCATE

Locates the first occurrence of any type of literal, or of an expression that uses any data type, or mix of data types. The table does not have to be indexed.

For example:

  • Vendor_City = "New York"

  • Invoice_Amount = 296.50

  • Invoice_Date = `20141231`

  • Vendor_City = v_city

  • Vendor_City = v_city AND Invoice_Amount > 1000

Locate Record

LOCATE RECORD

Locates a specific record number in a table.

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


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