Manipulating Text in MySQL

MySQL provides a number of built-in functions that can be used to both manipulate text values, and also to perform mathematical calculations on numerical values. The purpose of this chapter is to provide an overview of these functions.

MySQL provides wide selection of functions which may be called during a SQL statement to make changes to text values. The following table lists the most frequently used functions in this category. Some example of how to use these functions are included after the table. For extensive details on how to use each of these functions refer to the MySQL Reference Guide:

For example, we might want to convert the text returned by a SELECT statement to upper case:

mysql> select UPPER(prod_name) from product where prod_code=4;
+--------------------------+
| UPPER(prod_name)         |
+--------------------------+
| MICROSOFT 10-20 KEYBOARD |
+--------------------------+
1 row in set (0.00 sec)

Alternatively, we might be interested to know the length of a column value:

mysql> SELECT prod_name, LENGTH(prod_name) FROM product where prod_code=4;
+--------------------------+-------------------+
| prod_name                | LENGTH(prod_name) |
+--------------------------+-------------------+
| Microsoft 10-20 Keyboard |                24 |
+--------------------------+-------------------+
1 row in set (0.03 sec)

We could also replace one word with another:

mysql> SELECT  REPLACE(prod_name, 'Microsoft', 'Apple') FROM product where prod_code=4;
+------------------------------------------+
| REPLACE(prod_name, 'Microsoft', 'Apple') |
+------------------------------------------+
| Apple 10-20 Keyboard                     |
+------------------------------------------+
1 row in set (0.00 sec)