LOCATE( )

This MySQL function returns the numeric starting point of the first occurrence of a substring in the string supplied as a second argument.

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

LOCATE(substring, string[, start_position])

Explanation

This function returns the numeric starting point of the first occurrence of a substring in the string supplied as a second argument. A starting position for searching may be specified as a third argument. It's not case-sensitive unless one of the strings given is a binary string. The function is multi-byte safe.

Examples

As an example of this function's potential, supposed that a table for a college contains a list of courses and that one of the columns (course_desc) contains the description of the courses. A typical column starts like this:

Victorian Literature [19th Cent. Engl. Lit.]: This course covers Engl. novels and Engl. short-stories...

We want to replace all occurences of the abbreviation Engl. with English except in the begining of the strings where the abbreviation is contained in square brackets as shown here. To do this, we could enter an SQL statement like this:

UPDATE courses
SET course_desc =
INSERT(course_desc, LOCATE('Engl.', course_desc, LOCATE(']', course_desc)), 5, 'English')
WHERE course_desc LIKE '%Engl.%';

In this statement, we're using the LOCATE() function to locate the first occurence of the closing square bracket. From there we're using LOCATE() again to find the first occurence of Engl.. With the INSERT() function (not the INSERT statement), we're removing the five characters starting from that point located after the closing square bracket and inserting the text English. This is a bit complex, but it generally works. However, it only replaces one occurence of the text we're trying to replace, whereas in the sample text shown there are at least two occurences of Engl. after the brackets. We could keep running this SQL statement until we replace each one. A better method would be to run this SQL statement instead:

UPDATE courses
SET course_desc =
CONCAT(
   SUBSTRING_INDEX(course_desc, ']', 1),
   REPLACE( SUBSTR(course_desc, LOCATE(']', course_desc)),
   'Engl.', 'English')
)
WHERE course_desc LIKE '%Engl.%';

In this statement, we're using SUBSTRING_INDEX( ) to extract the opening text up until the first closing bracket. We're then using LOCATE( ) to locate the closing bracket, SUBSTR( ) to extract the text from that point forward, and then REPLACE( ) to replace all occurences of Engl. in that substring. Finally, CONCAT( ) pastes the opening text that we preserved and excluded from the replacement component together with the cleaned text.