SQL Reference - LOCATE

LOCATE(substr, str, pos)

substr: This is the substring to be located. It’s a string of characters that you want to find within another string.
str: This represents the string in which the search will be conducted. It is the main string where you are looking for the specified substring.
pos: This represents the position where the search starts. By default, the search starts at the beginning of the string. If a position is specified, the search starts from that position in the string.

Example

SELECT LOCATE('bar', 'foobarbar');

Output

Explanation

The LOCATE function in MySQL is used to find the position of the first occurrence of a substring in a string. In this example it returns 5, which indicates that the first occurrence of the substring ‘bar’ in the string ‘foobarbar’ starts at the 5th position.

CHARINDEX( expressionToFind ,expressionToSearch [ , start_location ] )

expressiontofind: This parameter represents the character string that needs to be located in the expressionToSearch. It can include pattern elements like wildcard characters.
expressiontosearch: This parameter represents the character string where SQL server will look for occurrence of the character string as defined in expressionToFind.
start_location: This is an optional parameter. It represents the position within expressionToSearch where the server starts to search for the character string defined in expressionToFind. By default, the search starts at the beginning.

Example

SELECT LOCATE('SQL', 'SQL Server');

Output

Explanation

The LOCATE function in this example is used to find the position of the first occurrence of the substring ‘SQL’ in the string ‘SQL Server’. The function returns the position of the first character of the substring. Hence, in this case, it returns 1, as the substring ‘SQL’ starts at the first character.

LOCATE(substring, string, [start_position])

substring: This is the fragment of text that will be searched for within the main string. It can be any sequence of characters contained within the string.
string: This is the main text within which the substring will be searched for. It is the context where the search operation will be performed.
start_position: This is an optional parameter which determines where the search operation should commence within the string. It is 1-indexed, meaning that the first character of the string has the position number 1. If left unspecified, the search starts at the beginning of the string.

Example

SELECT LOCATE('A', 'DATABASE') FROM DUAL;

Output

Explanation

The LOCATE function in Oracle returns the starting position of the first occurrence of a substring within a string. The function returns 0 if the substring is not found. In the above example, since the letter ‘A’ is not found in the word ‘DATABASE’, the function returns 0.