LOCATE string function to return the position of a substring - MaxCompute

Returns the position of the first occurrence of a substring within a string. Positions are 1-based.

Syntax

bigint locate(string <substr>, string <str>[, bigint <start_pos>])

Parameter	Required	Type	Description
`substr`	Yes	STRING	The substring to search for.
`str`	Yes	STRING	The string to search in.
`start_pos`	No	BIGINT	The position to start searching from.

Returns a BIGINT value representing the position of substr in str.

If substr is found, returns the position of its first occurrence. Positions start at 1.
If substr is not found in str, returns 0.
If str or substr is null, returns null.
If start_pos is null, returns 0.

Example 1: Find the position of ab in abchelloabc. The substring starts at position 1.

-- Returns 1
SELECT locate('ab', 'abchelloabc');

Example 2: Search for a substring that does not exist. The function returns 0.

-- Returns 0
SELECT locate('hi', 'abc,hello,ab,c');

Example 3: Pass null as start_pos. The function returns 0.

-- Returns 0
SELECT locate('ab', 'abhelloabc', NULL);

LOCATE is a string function. For other string search and conversion functions, see String functions.