AT( ) function

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


AT(occurrence_num, search_for_string, within_text)



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


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


Character. The value to search in.


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


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.


Example Return value

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


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


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


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

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


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

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


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.