Frequently Used String Functions in MySQL

MySQL's built-in string-related functions can be used several ways. You can use functions in SELECT statements without specifying a table to retrieve a result of the function. Or you can use functions to enhance your SELECT results by concatenating two fields to form a new string.

Length and Concatenation Functions

The group of length and concatenation functions focuses on the length of strings and concatenating strings together. Length-related functions include LENGTH(), OCTET_LENGTH(), CHAR_LENGTH(), and CHARACTER_LENGTH(), which do virtually the same thing: count characters in a string.

mysql> select length('This is cool!');
+-------------------------+
| LENGTH('This is cool!') |
+-------------------------+
|                      13 |
+-------------------------+
1 row in set (0.00 sec)

The fun begins with the CONCAT() function, which is used to concatenate two or more strings:

mysql> select concat('My', 'S', 'QL');
+-------------------------+
| CONCAT('My', 'S', 'QL') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

Imagine using this function with a table containing names, split into firstname and lastname fields. Instead of using two strings, use two field names to concatenate the firstname and the lastname fields. By concatenating the fields, you reduce the lines of code necessary to achieve the same result in your application:

mysql> select concat(firstname, lastname) from table_name;
+-----------------------------+
| CONCAT(firstname, lastname) |
+-----------------------------+
| JohnSmith                   |
| JaneSmith                   |
| JimboJones                  |
| AndySmith                   |
| ChrisJones                  |
| AnnaBell                    |
| JimmyCarr                   |
| AlbertSmith                 |
| JohnDoe                     |
+-----------------------------+
9 rows in set (0.00 sec)

mysql> select concat('firstname', 'lastname') FROM table_name;
+---------------------------------+
| CONCAT('firstname', 'lastname') |
+---------------------------------+
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
| firstnamelastname               |
+---------------------------------+
9 rows in set (0.00 sec)

The CONCAT() function would be useful if there were some sort of separator between the names, and that's where the next function comes in: CONCAT_WS().

As you may have figured out, CONTACT_WS() stands for concatenate with separator. The separator can be anything you choose, but the following example uses whitespace:

mysql> select concat_ws(' ', firstname, lastname) FROM table_name;
+-------------------------------------+
| CONCAT_WS(' ', firstname, lastname) |
+-------------------------------------+
| John Smith                          |
| Jane Smith                          |
| Jimbo Jones                         |
| Andy Smith                          |
| Chris Jones                         |
| Anna Bell                           |
| Jimmy Carr                          |
| Albert Smith                        |
| John Doe                            |
+-------------------------------------+
9 rows in set (0.00 sec)

If you want to shorten the width of your result table, you can use AS to name the custom result field:

mysql> select concat_ws(' ', firstname, lastname) AS fullname FROM table_name;
+--------------+
| fullname     |
+--------------+
| John Smith   |
| Jane Smith   |
| Jimbo Jones  |
| Andy Smith   |
| Chris Jones  |
| Anna Bell    |
| Jimmy Carr   |
| Albert Smith |
| John Doe     |
+--------------+
9 rows in set (0.00 sec)

Trimming and Padding Functions

MySQL provides several functions for adding and removing extra characters (including whitespace) from strings. The RTRIM() and LTRIM() functions remove whitespace from either the right or left side of a string:

mysql> select rtrim('stringstring ');
+--------------------------+
| RTRIM('stringstring   ') |
+--------------------------+
| stringstring             |
+--------------------------+
1 row in set (0.00 sec)

mysql> select ltrim(' stringstring');
+-------------------------+
| LTRIM(' stringstring')  |
+-------------------------+
| stringstring            |
+-------------------------+
1 row in set (0.00 sec)

You may have padded strings to trim if the string is coming out of a fixed-width field, and either doesn't need to carry along the additional padding or is being inserted into a varchar or other nonfixed-width field. If your strings are padded with a character besides whitespace, use the trIM() function to name the characters you want to remove. For example, to remove the leading X characters from the string XXXneedleXXX, use

mysql> select trim(leading 'X' from 'XXXneedleXXX');
+---------------------------------------+
| TRIM(LEADING 'X' from 'XXXneedleXXX') |
+---------------------------------------+
| needleXXX                             |
+---------------------------------------+
1 row in set (0.00 sec)

Use trAILING to remove the characters from the end of the string:

mysql> select trim(trailing 'X' from 'XXXneedleXXX');
+----------------------------------------+
| TRIM(TRAILING 'X' from 'XXXneedleXXX') |
+----------------------------------------+
| XXXneedle                              |
+----------------------------------------+
1 row in set (0.00 sec)

If neither LEADING nor trAILING is indicated, both are assumed:

mysql> select trim('X' from 'XXXneedleXXX');
+-------------------------------+
| TRIM('X' from 'XXXneedleXXX') |
+-------------------------------+
| needle                        |
+-------------------------------+
1 row in set (0.00 sec)

Just like RTRIM() and LTRIM() remove padding characters, RPAD() and LPAD() add characters to a string. For example, you may want to add specific identification characters to a string that is part of an order number, in a database used for sales. When you use the padding functions, the required elements are the string, the target length, and the padding character. For example, pad the string needle with the X character until the string is 10 characters long:

mysql> select rpad('needle', 10, 'X');
+-------------------------+
| RPAD('needle', 10, 'X') |
+-------------------------+
| needleXXXX              |
+-------------------------+
1 row in set (0.00 sec)

mysql> select lpad('needle', 10, 'X');
+-------------------------+
| LPAD('needle', 10, 'X') |
+-------------------------+
| XXXXneedle              |
+-------------------------+
1 row in set (0.00 sec)

Location and Position Functions

The group of location and position functions is useful for finding parts of strings within other strings. The LOCATE() function returns the position of the first occurrence of a given substring within the target string. For example, you can look for a needle in a haystack:

mysql> select locate('needle', 'haystackneedlehaystack');
+--------------------------------------------+
| LOCATE('needle', 'haystackneedlehaystack') |
+--------------------------------------------+
|                                          9 |
+--------------------------------------------+
1 row in set (0.00 sec)

The substring needle begins at position 9 in the target string. If the substring cannot be found in the target string, MySQL returns 0 as a result.

An extension of the LOCATE() function is to use a third argument for starting position. If you start looking for needle in haystack before position 9, you'll receive a result. Otherwise, because needle starts at position 9, you'll receive a 0 result if you specify a greater starting position:

mysql> select locate('needle', 'haystackneedlehaystack',6);
+----------------------------------------------+
| LOCATE('needle', 'haystackneedlehaystack',9) |
+----------------------------------------------+
|                                            9 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select locate('needle', 'haystackneedlehaystack',12);
+-----------------------------------------------+
| LOCATE('needle', 'haystackneedlehaystack',12) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (0.00 sec)

Substring Functions

If your goal is to extract a substring from a target string, several functions fit the bill. Given a string, starting position, and length, you can use the SUBSTRING() function. This example gets three characters from the string MySQL, starting at position 2:

mysql> select substring("MySQL", 2, 3);
+--------------------------+
| SUBSTRING("MySQL", 2, 3) |
+--------------------------+
| ySQ                      |
+--------------------------+
1 row in set (0.00 sec)

If you just want a few characters from the left or right ends of a string, use the LEFT() and RIGHT() functions:

mysql> select left("MySQL", 2);
+------------------+
| LEFT("MySQL", 2) |
+------------------+
| My               |
+------------------+
1 row in set (0.00 sec)

mysql> select right("MySQL", 3);
+-------------------+
| RIGHT("MySQL", 3) |
+-------------------+
| SQL               |
+-------------------+
1 row in set (0.00 sec)

One of the many common uses of substring functions is to extract parts of order numbers, to find out who placed the order. In some applications, the system is designed to automatically generate an order number, containing a date, customer identification, and other information. If this order number always follows a particular pattern, such as XXXX-YYYYY-ZZ, you can use substring functions to extract the individual parts of the whole. For example, if ZZ always represents the state to which the order was shipped, you can use the RIGHT() function to extract these characters and report the number of orders shipped to a particular state.

String Modification Functions

Your programming language of choice likely has functions to modify the appearance of strings, but if you can perform the task as part of the SQL statement, all the better.

The MySQL LCASE() and UCASE() functions transform a string into lowercase or uppercase:

mysql> select lcase('MYSQL');
+----------------+
| LCASE('MYSQL') |
+----------------+
| mysql          |
+----------------+
1 row in set (0.00 sec)

mysql> select ucase('mysql');
+----------------+
| UCASE('mysql') |
+----------------+
| MYSQL          |
+----------------+
1 row in set (0.00 sec)

Remember, if you use the functions with field names, don't use quotation marks:

mysql> select ucase(lastname) from table_name;
+-----------------+
| UCASE(lastname) |
+-----------------+
| BELL            |
| CARR            |
| DOE             |
| JONES           |
| JONES           |
| SMITH           |
| SMITH           |
| SMITH           |
| SMITH           |
+-----------------+
9 rows in set (0.00 sec)

Another fun string-manipulation function is REPEAT(), which does just what it sounds likerepeats a string for a given number of times:

mysql> select repeat("bowwow", 4);
+--------------------------+
| REPEAT("bowwow", 4)      |
+--------------------------+
| bowwowbowwowbowwowbowwow |
+--------------------------+
1 row in set (0.00 sec)

The REPLACE() function replaces all occurrences of a given string with another string:

mysql> select replace('bowwowbowwowbowwowbowwow', 'wow', 'WOW');
+---------------------------------------------------+
| REPLACE('bowwowbowwowbowwowbowwow', 'wow', 'WOW') |
+---------------------------------------------------+
| bowWOWbowWOWbowWOWbowWOW                          |
+---------------------------------------------------+
1 row in set (0.00 sec)