Changes

Manipulating Text in MySQL

1,172 bytes added, 14:55, 24 October 2007
MySQL String Manipulation Functions
</tr>
</table>
 
For example, we might want to convert the text returned by a SELECT statement to upper case:
 
<pre>
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)
</pre>
 
Alternatively, we might be interested to know the length of a column value:
 
<pre>
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)
</pre>
 
We could also replace one word with another:
 
<pre>
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)
</pre>