LOCATE - MaxCompute - Alibaba Cloud Documentation Center

Returns the position of substring substr in string str. You can use start_pos to specify the position from which the search starts. The value starts from 1.

Syntax

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

Parameters

substr: required. A value of the STRING type. This parameter specifies the substring that you want to search for.
str: required. A value of the STRING type. This parameter specifies the string in which you want to search for the substring.
start_pos: optional. A value of the BIGINT type. This parameter specifies the position from which the search starts.

Return value

A value of the BIGINT type is returned. The return value varies based on the following rules:

If substr cannot be found in str, 0 is returned.
If the value of str or substr is null, null is returned.
If the value of start_pos is null, 0 is returned.

Examples

Example 1: Return the position of substring ab in string abchelloabc. Sample statement:
```
-- The return value is 1. 
select locate('ab', 'abchelloabc');
```
Example 2: Return the position of substring hi in string abchelloabc. Sample statement:
```
-- The return value is 0. 
select locate('hi', 'abc,hello,ab,c');
```

Example 3: The value of start_pos is set to null. Sample statement:

-- The return value is 0. 
select locate('ab', 'abhelloabc', null);

Related functions

LOCATE is a string function. For more information about functions related to string searches and conversion, see String functions.