Working with Strings - MySQL - page 167

To combine rather than pull apart strings, use the CONCAT() function. It concatenates
its arguments and returns the result:
mysql> SELECT CONCAT(name,' ends in "d": ',IF(RIGHT(name,1)='d','YES','NO'))
-> AS 'ends in "d"?'
-> FROM metal;
+--------------------------+
| ends in "d"? |
+--------------------------+
| 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 |
| 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 |
+-------------+
| goldide |
| ironide |
| leadide |
| mercuryide |
| platinumide |
| tinide |
+-------------+
To undo the operation, strip the last three characters (the CHAR_LENGTH() function re‐
turns the length of a string in characters):
mysql> UPDATE metal SET name = LEFT(name,CHAR_LENGTH(name)-3);
mysql> SELECT name FROM metal;
+----------+
| name |
+----------+
| gold |
| iron |
| lead |
| mercury |
| platinum |
| 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