MySQL Cookbook

Decomposing or Combining Strings

Problem

You want to break apart a string to extract a substring 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

Parts of strings can be extracted and displayed. 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.^[23]

mysql> SELECT name, SUBSTRING(name,4), MID(name,4) FROM metal; +----------+-------------------+-------------+ | name | SUBSTRING(name,4) | MID(name,4) | +----------+-------------------+-------------+ ...