AT( ) function

Returns a number specifying the starting location of a particular occurrence of a substring within a character value.

Syntax

AT(occurrence_num, search_for_string, within_text)

Parameters

Name	Type	Description
occurance_num	numeric	The occurrence (instance) of search_for_string to return the location of. For example, specify 1 to return the starting location of the first occurrence of search_for_string.
search_for_string	character	The substring to search for in within_text. This value is case-sensitive. If search_for_string includes double quotation marks, you need to enclose the value in single quotation marks: AT(1,'"test"', Description)
within_text	character	The value to search in. You can concatenate two or more fields in the within_text parameter if you want to search in more than one field in a table: AT(1,'"test"', Description+Summary)

Name

Type

Description

occurance_num

numeric

The occurrence (instance) of search_for_string to return the location of.

For example, specify 1 to return the starting location of the first occurrence of search_for_string.

search_for_string

character

The substring to search for in within_text. This value is case-sensitive.

If search_for_string includes double quotation marks, you need to enclose the value in single quotation marks:

AT(1,'"test"', Description)

within_text

character

The value to search in.

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

AT(1,'"test"', Description+Summary)

Output

Numeric. Returns the starting byte position of the specified occurrence of the search_for_string value, or 0 if no matches are found.

Examples

Basic examples

Occurrences found

Returns 4:

AT(1, "-", "604-669-4225")

Returns 8:

AT(2, "-", "604-669-4225")

Occurrences not found

Returns 0, because there is not a third hyphen in the value:

AT(3, "-", "604-669-4225")

Returns 0, because there is not a fourth lowercase "a" in the value:

AT(4, "a", "Alabama")

Groups of characters

Returns 5:

AT(2, "iss", "Mississippi")

Searching a field

Returns the byte position of the first hyphen in each value in the Invoice_Number field:

AT(1, "-", Invoice_Number)

Advanced examples

Finding invoice numbers in which the second hyphen occurs after the tenth byte position

You can analyze the consistency of invoice numbers in a table by using the AT( ) function to create a filter like the one below. This filter isolates all records in which the invoice number contains two or more hyphens, and the second hyphen occurs after the tenth byte position:

SET FILTER TO AT(2, "-", Invoice_Number) > 10

Remarks

When to use AT( )

Use this function to retrieve the following starting positions within a character value:

the starting position of a substring
the starting position of a subsequent occurrence of the substring

If you only want to confirm multiple occurrences of the same substring in a field, the OCCURS( ) function is a better alternative. For more information, see OCCURS( ) function.

Return value when occurrence_num exceeds the number of occurrences

If occurrence_num is greater than the actual number of substring occurrences in within_text, the function returns 0 because it cannot find that occurrence of the substring.

Concatenated fields and return values

When you search in more than one field, the value returned for the instance is the starting location of search_for_string across all fields that you specify. The concatenated fields are treated like a single field that includes leading and trailing spaces from the individual fields, unless you use the ALLTRIM( ) function to remove spaces.

For example, if you search for the first occurrence of a string in two fields with a width of eight characters each, and the string is found at the beginning of the second field, the return value is 9.

[ Back to top ]

ACL Scripting Guide 14.1