A comparison of ACL search methods

Table 1 and Table 2 compare the different functions you can use to create a filter that isolates all records in a table that match the search criteria. For examples of expressions that use the functions, see Examples of search expressions. For detailed information about each function, see the ACL Language Reference.

Table 3 and Table 4 compare the different operations you can use to select the first record in a table that matches the search criteria.

Note

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

Table 1. Comparison of ACL search functions (continued in Table 2 . . .)

Data types searchable

Searches in

Searches in related fields

Index required

Leading spaces searchable

Case-

sensitive

FIND( )

Character

Field

Fields

Record

Yes

(fully qualified field name must be specified)

No

Yes

(leading spaces in data can optionally be matched in search string)

No

MATCH( )

Character

Datetime

Numeric

Field

Fields

Yes

(leading spaces in data must be exactly matched in search string)

Yes

MAP( )

Character

Yes

(literal characters)

No

(wildcard characters)

OCCURS( )

Yes

(leading spaces in data can optionally be matched in search string)

Yes

AT( )

BETWEEN( )

Character

Datetime

Numeric

Field

REGEXFIND( )

Character

Field

Fields

Yes

(spaces in data or search string treated like a character)

ISFUZZYDUP( )

LEVDIST( )

Field

No

(ISFUZZYDUP)

Optional

(LEVDIST)

Table 2. Comparison of ACL search functions (. . . continued from Table 1)

Partial matching supported

Multiple search terms supported

Quotation marks around search term required

Affected by Exact Character Comparisons option

(SET EXACT ON/OFF)

Expressions supported

FIND( )

Yes

(search string can appear anywhere in the field)

No

Yes

No

Yes

MATCH( )

Yes

(search string must appear at the start of the field, Character only)

Yes

Yes

(backquotes for Datetime, none for Numeric)

Yes

MAP( )

Yes

(search string must be same length as data value, or shorter)

No

Yes

No

OCCURS( )

Yes

(search string can appear anywhere in the field)

AT( )

BETWEEN( )

Yes

(search string must appear at the start of the field, Character only)

Yes

(backquotes for Datetime, none for Numeric)

Yes

REGEXFIND( )

Yes

(search string can appear anywhere in the field)

Yes

Yes

No

ISFUZZYDUP( )

LEVDIST( )

Yes

No

 

Table 3. Comparison of ACL search operations (commands) (continued in Table 4 . . .)

Data types searchable

Searches in

Searches in related fields

Index required

Leading spaces searchable

Case-

sensitive

Find Literal

Character

Field

Yes

Yes

(ascending order required)

No

Yes

Seek Expression

Yes

(spaces in data or search string treated like a character)

Locate If / Locate Record

Character

Datetime

Numeric

(you can also search by record number)

Field

Fields

Yes

(fully qualified field name must be specified)

No

Table 4. Comparison of ACL search operations (commands) (. . . continued from Table 3)

Partial matching supported

Quotation marks around search term required

Affected by Exact Character Comparisons option

(SET EXACT ON/OFF)

Expressions supported

Additional remarks

Find Literal

Yes

(search string must appear at the start of the field)

No

(search term must not be enclosed in quotation marks, unless the quotation marks are part of the data)

No

No

The Find Literal operation in the Search dialog box, and the FIND command, are identical.

Seek Expression

Yes

Yes

The Seek Expression operation in the Search dialog box, and the SEEK command, are identical.

Locate If / Locate Record

Yes

(search string must appear at the start of the field, Character only)

Yes (for Character)

No (for Numeric)

Optional (for record number)

backquotes (for Datetime)

Yes

The Locate If and the Locate Record operations in the Search dialog box, and the LOCATE/LOCATE RECORD command, are identical.

Related concepts
About searching data
Related tasks
Searching data to isolate all matching records
Searching data for the first matching record
Related reference
Examples of search expressions


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