How the LOCATE() function works in Mariadb?

The LOCATE() function is a string function that returns the position of the first occurrence of a substring within a string. It is similar to the INSTR() function, except that it can accept an optional third argument that specifies the starting position of the search. The LOCATE() function is case-insensitive by default, unless the collation of the string arguments is case-sensitive.

Syntax

The syntax of the LOCATE() function is as follows:

LOCATE(substring, string, [position])

The substring argument is the substring to be searched for within the string argument. The string argument is the string to be searched. The position argument is an optional integer value that indicates the position from which to start the search. If the position argument is omitted or negative, it is treated as 1. If the position argument is greater than the length of the string argument, the function returns 0.

The LOCATE() function returns an integer value that represents the position of the first occurrence of the substring within the string, starting from the position. If the substring is not found, the function returns 0. If either the substring or the string argument is NULL, the function returns NULL.

Examples

Example 1: Basic usage of the LOCATE() function

The following example shows how to use the LOCATE() function without the position argument. It returns the position of the first occurrence of the substring ‘cat’ within the string ‘The cat is on the mat’.

SELECT LOCATE('cat', 'The cat is on the mat');

The output is:

5

This means that the substring ‘cat’ is found at the 5th position of the string ‘The cat is on the mat’.

Example 2: Using the position argument

The following example shows how to use the LOCATE() function with the position argument. It returns the position of the first occurrence of the substring ‘cat’ within the string ‘The cat is on the mat’, starting from the 6th position.

SELECT LOCATE('cat', 'The cat is on the mat', 6);

The output is:

0

This means that the substring ‘cat’ is found at the 16th position of the string ‘The cat is on the mat’, starting from the 6th position.

Example 3: Using the position argument with a value greater than the length of the string

The following example shows what happens when the LOCATE() function is used with the position argument that is greater than the length of the string argument. It returns 0, indicating that the substring is not found.

SELECT LOCATE('cat', 'The cat is on the mat', 20);

The output is:

0

This means that the substring ‘cat’ is not found within the string ‘The cat is on the mat’, starting from the 20th position.

Related Functions

There are some other functions that are related to the LOCATE() function in Mariadb. They are:

• INSTR(): This function returns the position of the first occurrence of a substring within a string. It is similar to the LOCATE() function, except that it does not accept the position argument. It always starts the search from the beginning of the string.
• POSITION(): This function is a synonym for the LOCATE() function. It has the same syntax and behavior as the LOCATE() function.
• SUBSTRING_INDEX(): This function returns a substring from a string before a specified number of occurrences of a delimiter. It can be used to extract a part of a string based on the position of a delimiter.
• SUBSTRING(): This function returns a substring from a string starting from a specified position and optionally with a specified length. It can be used to extract a part of a string based on the position and length.

Here are some examples of using these related functions:

-- Get the position of the first occurrence of 'cat' within 'The cat is on the mat'
SELECT INSTR('The cat is on the mat', 'cat');

-- Get the position of the first occurrence of 'cat' within 'The cat is on the mat', starting from the 6th position
SELECT POSITION('cat' IN 'The cat is on the mat' FROM 6);

-- Get the substring from 'The cat is on the mat' before the 2nd occurrence of ' '
SELECT SUBSTRING_INDEX('The cat is on the mat', ' ', 2);

-- Get the substring from 'The cat is on the mat' starting from the 5th position with a length of 3
SELECT SUBSTRING('The cat is on the mat', 5, 3);

Conclusion

In this article, we have learned how the LOCATE() function works in Mariadb. We have seen its syntax, examples, and related functions. We have also learned how to use the optional position argument to specify the starting position of the search. The LOCATE() function is a useful function to find the position of a substring within a string. It can be used for various purposes, such as searching, parsing, replacing, etc.