Table of Contents for

MySQL in a Nutshell, 2nd Edition

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. However, it replaces only one occurrence of the text we’re trying to replace, whereas in the sample text shown there are at least two occurrences of Engl. after the brackets. We could keep running that 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 use SUBSTRING_INDEX() to extract the opening text until the first closing bracket. We then use LOCATE() to locate the closing bracket, SUBSTR() to extract the text from that point forward, and then REPLACE() to replace all occurrences 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.