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) |
+----------+-------------------+-------------+
| copper   | per               | per         |
| gold     | d                 | d           |
| iron     | n                 | n           |
| lead     | d                 | d           |
| mercury  | cury              | cury        |
| platinum | tinum             | tinum       |
| silver   | ver               | ver         |
| tin      |                   |             |
+----------+-------------------+-------------+

To return everything to the right or left of a given character, use SUBSTRING_INDEX(str,c,n). It searches into a string str for the n-th occurrence of the character c and returns everything to its left. If n is negative, the search for c starts from the right and returns everything to the right of the character:

mysql> SELECT name,
    -> SUBSTRING_INDEX(name,'r',2),
    -> SUBSTRING_INDEX(name,'i',-1)
    -> FROM metal;
+----------+-----------------------------+------------------------------+
| name     | SUBSTRING_INDEX(name,'r',2) | SUBSTRING_INDEX(name,'i',-1) |
+----------+-----------------------------+------------------------------+
| copper   | copper                      | copper                       |
| gold     | gold                        | gold                         |
| iron     | iron                        | ron                          |
| lead     | lead                        | lead                         |
| mercury  | mercu                       | mercury                      |
| platinum | platinum                    | num                          |
| silver   | silver                      | lver                         |
| tin      | tin                         | n                            |
+----------+-----------------------------+------------------------------+

Note that if there is no n-th occurrence of the character, SUBSTRING_INDEX( ) returns the entire string. SUBSTRING_INDEX( ) is case sensitive.

Substrings can be used for purposes other than display, such as to perform comparisons. The following query finds metal names having a first letter that lies in the last half of the alphabet:

mysql> SELECT name from metal WHERE LEFT(name,1) >= 'n';
+----------+
| name     |
+----------+
| platinum |
| silver   |
| tin      |
+----------+

To combine strings rather than pull them apart, use the CONCAT( ) function. It concatenates all its arguments and returns the result:

mysql> SELECT CONCAT('Hello, ',USER( ),', welcome to MySQL!') AS greeting;
+------------------------------------------+
| greeting                                 |
+------------------------------------------+
| Hello, paul@localhost, welcome to MySQL! |
+------------------------------------------+
mysql> SELECT CONCAT(name,' ends in "d": ',IF(RIGHT(name,1)='d','YES','NO'))
    -> AS 'ends in "d"?'
    -> FROM metal;
+--------------------------+
| ends in "d"?             |
+--------------------------+
| copper ends in "d": NO   |
| gold ends in "d": YES    |
| iron ends in "d": NO     |
| lead ends in "d": YES    |
| mercury ends in "d": NO  |
| platinum ends in "d": NO |
| silver ends in "d": NO   |
| tin ends in "d": NO      |
+--------------------------+

Concatenation can be useful for modifying column values “in place.” For example, the following UPDATE statement adds a string to the end of each name value in the metal table:

mysql> UPDATE metal SET name = CONCAT(name,'ide');
mysql> SELECT name FROM metal;
+-------------+
| name        |
+-------------+
| copperide   |
| goldide     |
| ironide     |
| leadide     |
| mercuryide  |
| platinumide |
| silveride   |
| tinide      |
+-------------+

To undo the operation, strip off the last three characters (the LENGTH( ) function returns the length of a string):

mysql> UPDATE metal SET name = LEFT(name,LENGTH(name)-3);
mysql> SELECT name FROM metal;
+----------+
| name     |
+----------+
| copper   |
| gold     |
| iron     |
| lead     |
| mercury  |
| platinum |
| silver   |
| tin      |
+----------+

The concept of modifying a column in place can be applied to ENUM or SET values as well, which usually can be treated as string values even though they are stored internally as numbers. For example, to concatenate a SET element to an existing SET column, use CONCAT( ) to add the new value to the existing value, preceded by a comma. But remember to account for the possibility that the existing value might be NULL or the empty string. In that case, set the column value equal to the new element, without the leading comma:

UPDATE tbl_name
SET set_col = IF(set_col IS NULL OR set_col = '',val,CONCAT(set_col,',',val));

---

^[23] MID( ) acts the same way if you omit its third argument, because MID( ) is actually a synonym for SUBSTRING( ).