MySQL LOCATE Function with Examples


How to Use the MySQL LOCATE Function

In this tutorial, we'll learn how to use the MySQL LOCATE function. LOCATE returns the position of the first occurrence of substring in a given string.

MySQL LOCATE() Syntax

The following is the syntax of the LOCATE function:

LOCATE(substring, string)

or:

LOCATE(substring, string, position)

Remarks

  • LOCATE returns 0 if substring or string is NULL, and returns 0 if substring is not in string.
  • The first syntax returns the position of the first occurrence of substring in string, starting at the 1st position, and the second syntax returns the position of the first occurrence of substring in string, starting at the specified position.

LOCATE() Examples

Example 1

The following statement returns the position of the first occurrence of tutorial:

SELECT LOCATE("tutorial", "Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use LEFT JOIN, and the second tutorial teaches you how to use RIGHT JOIN.") AS Position;

Result: 35

The are 3 tutorial words in the specified string above, but the first occurrence is located at the 35th position.

Example 2

The following statement returns the position of the first occurrence of guide:

SELECT LOCATE("guide","Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use LEFT JOIN, and the second tutorial teaches you how to use RIGHT JOIN.") AS Position;

Result:

0

Since there is no words guide in the specified string above, LOCATE returns 0.

Example 3

The following statement returns the position of the first occurrence of tutorial, starting at the 30th position:

SELECT LOCATE("tutorial", "Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use INNER JOIN, and the second tutorial teaches you how to use FULL JOIN.", 30) AS Position;

Result:

35

Example 4

The following statement returns the position of the first occurrence of tutorial, starting at the 36th position:

SELECT LOCATE("tutorial", "Our website provides a lot of SQL tutorials. The first tutorial teaches you how to use INNER JOIN, and the second tutorial teaches you how to use FULL JOIN.", 36) AS Position;

Result:

56

In this tutorial, we've learned how to use the MySQL LOCATE function. LOCATE is a function to return the position of the first occurrence of substring in a given string. We can use the LOCATE function to get the position of the first occurrence of substring in a given string, starting at the 1st position or specified position.

Leave a Reply

Your email address will not be published. Required fields are marked *