View source for Manipulating Text in MySQL

<table border="0" cellspacing="0" width="100%"><tr>
<td width="20%">[[MySQL Calculations and Concatenations|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[MySQL Mathematical Functions|Next]]</td>
<tr>
<td width="20%">MySQL Calculations and Concatenations<td align="center"><td width="20%" align="right">MySQL Mathematical Functions</td>
</table>
<hr>


<htmlet>mysql</htmlet>


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 String Manipulation 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 [http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/string-functions.html the MySQL Reference Guide]:

<htmlet>adsdaqbox_flow</htmlet>
<table border="1" cellspacing="0">
<tr style="background:#efefef;">
<th><span class="bold"><strong>Name</strong></span></th>
<th><span class="bold"><strong>Description</strong></span></th>
</tr>
<tr>
<td>ASCII()</td>

<td>Returns numeric value of left-most character</td>
</tr>
<tr>
<td>BIN()</td>
<td>Returns a string representation of the argument</td>
</tr>
<tr>
<td>BIT_LENGTH()</td>
<td>Returns length of argument in bits</td>
</tr>
<tr>
<td>CHAR_LENGTH()</td>

<td>Returns number of characters in argument</td>
</tr>
<tr>
<td>CHAR()</td>
<td>Returns the character for each integer passed</td>
</tr>
<tr>
<td>CHARACTER_LENGTH()</td>
<td>The same as CHAR_LENGTH()</td>
</tr>
<tr>
<td>CONCAT_WS()</td>

<td>Returns concatenate with separator</td>
</tr>
<tr>
<td>CONCAT()</td>
<td>Returns concatenated string</td>
</tr>
<tr>
<td>CONV()</td>
<td>Converts numbers between different number bases</td>
</tr>
<tr>
<td>ELT()</td>

<td>Returns string at index number</td>
</tr>
<tr>
<td>&lt;=&gt;</td>
<td>NULL-safe equal to </td>
</tr>
<tr>
<td>=</td>
<td>Equal </td>
</tr>
<tr>
<td>EXPORT_SET()</td>

<td>Returns a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string</td>
</tr>
<tr>
<td>FIELD()</td>
<td>Returns the index (position) of the first argument in the subsequent arguments</td>
</tr>
<tr>
<td>FIND_IN_SET()</td>
<td>Returns the index position of the first argument within the second argument</td>
</tr>
<tr>
<td>FORMAT()</td>

<td>Returns a number formatted to specified number of decimal places</td>
</tr>
<tr>
<td>&gt;=</td>
<td>Greater than or equal </td>
</tr>
<tr>
<td>&gt;</td>
<td>Greater than </td>
</tr>
<tr>
<td>HEX()</td>

<td>Returns a string representation of a hex value</td>
</tr>
<tr>
<td>INSERT()</td>
<td>Insert a substring at the specified position up to the specified number of characters</td></tr>
<tr>
<td>INSTR()</td>
<td>Returns the index of the first occurrence of substring</td>
</tr>
<tr>
<td>IS NULL</td>

<td>NULL value test</td>
</tr>
<tr>
<td>IS</td>
<td>Test a value against a boolean</td>
</tr>
<tr>
<td>LCASE()</td>
<td>The same as LOWER() </td>
</tr>
<tr>
<td>LEFT()</td>

<td>Returns the leftmost number of characters as specified</td>
</tr>
<tr>
<td>LENGTH()</td>
<td>Returns the length of a string in bytes</td>
</tr>
<tr>
<td>&lt;=</td>
<td>Less than or equal </td>
</tr>
<tr>
<td>&lt;</td>

<td>Less than </td>
</tr>
<tr>
<td>LIKE</td>
<td>Simple pattern matching</td>
</tr>
<tr>
<td>LOAD_FILE()</td>
<td>Load the named file</td>
</tr>
<tr>
<td>LOCATE()</td>

<td>Returns the position of the first occurrence of substring</td>
</tr>
<tr>
<td>LOWER()</td>
<td>Returns the argument in lowercase </td>
</tr>
<tr>
<td>LPAD()</td>
<td>Returns the string argument, left-padded with the specified string</td>
</tr>
<tr>
<td>LTRIM()</td>

<td>Removes leading spaces</td>
</tr>
<tr>
<td>MAKE_SET()</td>
<td>Returns a set of comma-separated strings that have the corresponding bit in bits set</td>
</tr>
<tr>
<td>MID()</td>
<td>Returns a substring starting from the specified position</td>
</tr>
<tr>
<td>

!=, &lt;&gt;
</td>
<td>Not equal </td>
</tr>
<tr>
<td>NOT LIKE</td>
<td>Negation of simple pattern matching</td>
</tr>
<tr>
<td>NOT REGEXP</td>
<td>Negation of REGEXP</td>

</tr>
<tr>
<td>OCT()</td>
<td>Returns a string representation of the octal argument</td>
</tr>
<tr>
<td>OCTET_LENGTH()</td>
<td>The same as LENGTH()</td>
</tr>
<tr>
<td>ORD()</td>
<td>If the leftmost character of the argument is a multi-byte character, returns the code for that character</td>

</tr>
<tr>
<td>POSITION()</td>
<td>The same as LOCATE()</td>
</tr>
<tr>
<td>QUOTE()</td>
<td>Escape the argument for use in an SQL statement</td>
</tr>
<tr>
<td>REGEXP</td>
<td>Pattern matching using regular expressions</td>

</tr>
<tr>
<td>REPEAT()</td>
<td>Repeat a string the specified number of times</td>
</tr>
<tr>
<td>REPLACE()</td>
<td>Replace occurrences of a specified string</td>
</tr>
<tr>
<td>REVERSE()</td>
<td>Reverse the characters in a string</td>

</tr>
<tr>
<td>RIGHT()</td>
<td>Returns the specified rightmost number of characters</td>
</tr>
<tr>
<td>RLIKE</td>
<td>The same as REGEXP</td>
</tr>
<tr>
<td>RPAD()</td>
<td>Append string the specified number of times</td>

</tr>
<tr>
<td>RTRIM()</td>
<td>Removes trailing spaces</td>
</tr>
<tr>
<td>SOUNDEX()</td>
<td>Returns a soundex string (an alphanumeric string representing the phonetic representation of the word. Useful for finding words that sound similar)</td>
</tr>
<tr>
<td>
SOUNDS LIKE()</td>

<td>Compares sounds</td>
</tr>
<tr>
<td>SPACE()</td>
<td>Returns a string of the specified number of spaces</td>
</tr>
<tr>
<td>STRCMP()</td>
<td>Compares two strings</td>
</tr>
<tr>
<td>SUBSTRING_INDEX()</td>

<td>Returns a substring from a string before the specified number of occurrences of the delimiter</td>
</tr>
<tr>
<td>
SUBSTRING(), SUBSTR()
</td>
<td>Returns the substring as specified</td>
</tr>
<tr>
<td>TRIM()</td>
<td>Removes leading and trailing spaces</td>

</tr>
<tr>
<td>UCASE()</td>
<td>The same as UPPER()</td>
</tr>
<tr>
<td>
UNHEX()()</td>
<td>Converts each pair of hexadecimal digits to a character</td>
</tr>
<tr>
<td>UPPER()</td>

<td>Converts to uppercase</td>
</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>

<htmlet>mysql</htmlet>


<htmlet>ezoicbottom</htmlet>
<hr>
<table border="0" cellspacing="0" width="100%"><tr>
<td width="20%">[[MySQL Calculations and Concatenations|Previous]]<td align="center">[[MySQL Essentials|Table of Contents]]<td width="20%" align="right">[[MySQL Mathematical Functions|Next]]</td>
<tr>
<td width="20%">MySQL Calculations and Concatenations<td align="center"><td width="20%" align="right">MySQL Mathematical Functions</td>
</table>