AT( ) function

Returns a number specifying where a particular occurrence of a substring starts within a character value.

Syntax

AT(occurrence_num, search_for_string, within_text)

Parameters

occurrence_num

Numeric. The occurrence (instance) of the substring to search for.

search_for_string

Character. The substring to search for. This parameter is case-sensitive. Character literal values must be enclosed in quotation marks.

within_text

Character. The value to search in.

Output

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

Remarks

You can use this function to retrieve the starting position of a substring, or the starting position of a subsequent occurrence of the substring, within a character value. If you only want to confirm multiple occurrences of the same substring in a field, the OCCURS( ) function is a better alternative.

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

If the value of the search_for_string parameter includes double quotation marks, you need to enclose the entire parameter in single quotation marks.

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.

Examples

Example Return value

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

4

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

8

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

0

The return value is 0 because there is not a third hyphen in the value.

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

0

The return value is 0 because there is not a fourth lowercase “a” in the value.

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

5

AT(1, "-", Invoice_Number)

The byte position of the first hyphen in each value in the Invoice_Number field

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:

AT(2, "-", Invoice_Number) > 10
Related reference
OCCURS( ) function


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