Table of Contents for

MySQL in a Nutshell, 2nd Edition

This function returns the characters of a given string, starting from the position specified in the second argument. The first character is numbered 1. You can limit the length of the string retrieved by specifying a limit in the third argument. This function is similar to SUBSTRING().

As an example of this function, suppose that a table of information about teachers contains a column listing their home telephone numbers. This column’s entries are in a format showing only numbers, no hyphens or other separators (e.g., 50412345678). Suppose further that we decide to add the country code and hyphens in a typical U.S. format (e.g., +1-504-123-45678) because although all our teachers live in the U.S., we’re about to acquire a small school in a different country. We could make these changes like so:

UPDATE teachers
SET phone_home =
CONCAT_WS('-', '+1',
   LEFT(phone_home, 3),
   MID(phone_home, 4, 3),
   MID(phone_home, 7) );

This convoluted SQL statement extracts each component of the telephone number with the LEFT() and MID() functions. Using CONCAT_WS(), the data is merged back together along with the country code at the beginning. Components in the return value are separated with a hyphen, which is given as its first parameter.