MySQL Cookbook, 2nd Edition

Breaking Apart or Combining Strings

Problem

You want to extract a piece of a string or combine strings to form a larger string.

Solution

To obtain a piece of a string, use a substring-extraction function. To combine strings, use CONCAT().

Discussion

Strings can be broken apart by using appropriate substring-extraction functions. For example, LEFT(), MID(), and RIGHT() extract substrings from the left, middle, or right part of a string:

mysql>SELECT name, LEFT(name,2), MID(name,3,1), RIGHT(name,3) FROM metal;
+----------+--------------+---------------+---------------+
| name     | LEFT(name,2) | MID(name,3,1) | RIGHT(name,3) |
+----------+--------------+---------------+---------------+
| copper   | co           | p             | per           |
| gold     | go           | l             | old           |
| iron     | ir           | o             | ron           |
| lead     | le           | a             | ead           |
| mercury  | me           | r             | ury           |
| platinum | pl           | a             | num           |
| silver   | si           | l             | ver           |
| tin      | ti           | n             | tin           |
+----------+--------------+---------------+---------------+

For LEFT() and RIGHT(), the second argument indicates how many characters to return from the left or right end of the string. For MID(), the second argument is the starting position of the substring you want (beginning from 1), and the third argument indicates how many characters to return.

The SUBSTRING() function takes a string and a starting position, returning everything to the right of the position. MID() acts the same way if you omit its third argument because MID() is actually a synonym for SUBSTRING():

mysql>SELECT name, SUBSTRING(name,4), MID(name,4) ...