Locate

locate locates a substring that you specify within another string and returns the number of characters until the first character in the substring. The function returns 0 if it doesn't find the substring. The function is 1-based.

Syntax

locate(expression, substring, start)

Arguments

expression

The expression must be a string. It can be the name of a field that uses the string data type, a literal value like '12 Main Street', or a call to another function that outputs a string.

substring

The set of characters in expression that you want to locate. The substring can occur one or more times in expression.

start

(Optional) If substring occurs more than once, use start to identify where in the string the function should start looking for the substring. For example, suppose that you want to find the second example of a substring and you think it typically occurs after the first ten characters. You specify a start value of 10. It should start from 1.

Return type

Integer

Examples

The following example returns information about where the first occurrence of the substring “and” appears in a string.

locate('1 and 2 and 3 and 4', 'and')

The following value is returned.

3

The following example returns information about where the first occurrence of the substring “and” appears in a string after the fourth character.

locate('1 and 2 and 3 and 4', 'and', 4)

The following value is returned.

9