MySQL in a Nutshell, 2nd Edition

Name

LOCATE()

Synopsis

LOCATE(substring, string[, start_position])

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 multibyte-safe.

As an example of this function’s potential, suppose that a table for a college contains a list of courses and 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 occurrences of the abbreviation Engl. with English except in the beginning 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 use the LOCATE() function to locate the first occurrence of the closing square bracket. From there, we use LOCATE() again to find the first occurrence of Engl.. With the INSERT() function (not the INSERT statement), we remove 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. ...