String Functions

MySQL includes many functions that can be used to manipulate strings. You learn the most useful functions in this section.

Refer to the online manual for the full list of string functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html.

Concatenation

Joining two or more strings is known as concatenation. It is performed using the CONCAT() function.

 mysql> SELECT CONCAT('exam', 'ple'); +-----------------------+ | CONCAT('exam', 'ple') | +-----------------------+ | example               | +-----------------------+ 1 row in set (0.00 sec) 

The CONCAT() function can accept more than two arguments and returns a single string made up of each argument, in turn. Consider the following example:

 mysql> SELECT CONCAT('Catch', 'a', 'falling', 'star'); +-----------------------------------------+ | CONCAT('Catch', 'a', 'falling', 'star') | +-----------------------------------------+ | Catchafallingstar                       | +-----------------------------------------+ 1 row in set (0.00 sec) 

All the string arguments are concatenated, and the result is as shown. No spaces are included in the concatenated string unless they are contained in the string arguments themselves.

Often you will want to separate the concatenated values with a character. You can do so using the CONCAT_WS() function (the WS part stands for "with separator"), taking the separator character as the first argument.

Compare the output of the following to the previous example, in which CONCAT_WS() is used with a space character to separate the words in the result.

 mysql> SELECT CONCAT_WS(' ', 'Catch', 'a', 'falling', 'star'); +-------------------------------------------------+ | CONCAT_WS(' ', 'Catch', 'a', 'falling', 'star') | +-------------------------------------------------+ | Catch a falling star                            | +-------------------------------------------------+ 1 row in set (0.00 sec) 

Concatenation

Some database systems enable you to perform concatenation using an operatorusually the + or || symbols. In MySQL, however, you must use the CONCAT() function.

Trimming and Padding Strings

Sometimes you will be working with strings that contain excess whitespace characters. To trim space characters from the beginning or end of a string, use LTRIM() and RTRIM(). LTRIM() removes characters from the left side of the string, and RTRIM() from the right.

 mysql> SELECT LTRIM('   MySQL'), RTRIM('MySQL    '); +-------------------+--------------------+ | LTRIM('   MySQL') | RTRIM('MySQL    ') | +-------------------+--------------------+ | MySQL             | MySQL              | +-------------------+--------------------+ 1 row in set (0.00 sec) 

Trimming

In print, you cannot actually see that trailing space characters have been trimmed by RTRIM()you'll just have to trust that they have!

The TRIM() function has a different syntax that enables you to trim other characters than whitespace from eitheror bothends of a string. The keywords LEADING, trAILING, and BOTH are used to specify which part of the string to trim, as shown in the following examples:

 mysql> SELECT TRIM(LEADING '/' FROM '/dev/null'); +------------------------------------+ | TRIM(LEADING '/' FROM '/dev/null') | +------------------------------------+ | dev/null                           | +------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TRIM(TRAILING '.' FROM 'To be continued...'); +----------------------------------------------+ | TRIM(TRAILING '.' FROM 'To be continued...') | +----------------------------------------------+ | To be continued                              | +----------------------------------------------+ 1 row in set (0.00 sec) 

The opposite of trimming a string is padding it. MySQL provides the LPAD() and RPAD() functions, which pad a string to a fixed length by inserting characters on the left and right sides, respectively.

The arguments are the string to pad, the required length, and the padding character. The following example shows RPAD() being used on a database column to pad the name to 10 characters using periods:

 mysql> SELECT RPAD(last_name, 10, '.')     -> FROM customer_contacts     -> WHERE customer_code = 'SCICORP'; +--------------------------+ | RPAD(last_name, 10, '.') | +--------------------------+ | Einstein..               | | Darwin....               | | Curie.....               | | Franklin..               | +--------------------------+ 4 rows in set (0.00 sec)  

Search and Replace

The LOCATE() function returns the position of a substring within a string. The value returned is the character position of the first occurrence of the substring within the string, or zero if it is not found. The LENGTH() function returns the total number of characters in a string.

The following query shows some of the email addresses in the customer_contacts table. The second column shows the character position of the @ sign in each address, and the third column shows the total length of the string.

 mysql> SELECT email, LOCATE('@', email), LENGTH(email)     -> FROM customer_contacts     -> WHERE customer_code = 'PRESINC'; +-----------------------------+--------------------+--------+ | email                       | LOCATE('@', email) | LENGTH | +-----------------------------+--------------------+--------+ | [e-mail]   |                  8 |     25 | | [e-mail]     |                  6 |     23 | | [e-mail]       |                  4 |     21 | | [e-mail] |                 10 |     27 | +-----------------------------+--------------------+--------+ 4 rows in set (0.00 sec) 

The REPLACE() function can be used to replace one substring with another within a string. In the following example, the @ sign in an email address is replaced by the word at.

 mysql> SELECT email, REPLACE(email, '@', ' at ')     -> FROM customer_contacts     -> WHERE customer_code = 'PRESINC'; +-----------------------------+--------------------------------+ | email                       | REPLACE(email, '@', ' at ')    | +-----------------------------+--------------------------------+ | [e-mail]   | lincoln at presidentsinc.com   | | [e-mail]     | nixon at presidentsinc.com     | | [e-mail]       | fdr at presidentsinc.com       | | [e-mail] | roosevelt at presidentsinc.com | +-----------------------------+--------------------------------+ 4 rows in set (0.00 sec)  

Breaking Up a String

To return only a fixed portion of a string, use SUBSTRING(). The arguments are a string, a character position, and a length. The portion of the string beginning at the position is returned, up to the maximum length given.

The following query returns product names from the database, up to a maximum of 10 characters:

 mysql> SELECT SUBSTRING(name, 1, 10)     -> FROM products; +------------------------+ | SUBSTRING(name, 1, 10) | +------------------------+ | Small prod             | | Medium pro             | | Large prod             | +------------------------+ 3 rows in set (0.00 sec) 

Substring Shortcuts

If you pass a negative position argument to SUBSTRING() it counts backward from the end of the string to find the start position.

If you omit the length argument, the substring returned is taken from the given start position to the end of the string.

SUBSTRING() is useful when you want to split a string based on fixed character positions. To split a string based on the actual character values within it, MySQL provides the SUBSTRING_INDEX() function, so you don't have to use a clumsy combination of SUBSTRING() and LOCATE().

SUBSTRING_INDEX() takes a string argument followed by a delimiter character and the number of parts to return. After you break up the string using the delimiter, that number of parts is returned as a single string.

The following example returns the suffix from an Internet domain name:

 mysql> SELECT SUBSTRING_INDEX('www.samspublishing.com', '.',-1); +-----------------------------------------------------+ | SUBSTRING_INDEX('www.samspublishing.com', '.', -1)  | +-----------------------------------------------------+ | com                                                 | +-----------------------------------------------------+ 1 row in set (0.00 sec)  

Delimiters

In the previous example, if you were sure that domain suffixes are always three characters long, you could use SUBSTRING() with a -3 start position.

However, because the suffix might be .com, .name, .ca, and so on, the period delimiter must be used instead of a fixed character position.

Case Conversion

To convert a string to all lower or upper case, use LOWER() and UPPER(). The following example shows the same column value converted to both cases:

 mysql> SELECT UPPER(name), LOWER(name)     -> FROM customers; +-------------------------+-------------------------+ | UPPER(name)             | LOWER(name)             | +-------------------------+-------------------------+ | PRESIDENTS INCORPORATED | presidents incorporated | | SCIENCE CORPORATION     | science corporation     | | MUSICIANS OF AMERICA    | musicians of america    | +-------------------------+-------------------------+ 3 rows in set (0.00 sec) 

No single function exists in MySQL to capitalize only the first letter of a string, as you might often want to do with a person's name. The following shows how you can nest functions, combining LOWER() and UPPER() with SUBSTRING() and CONCAT() to produce this result:

 mysql> SELECT CONCAT(UPPER(SUBSTRING(name, 1, 1)),     ->               LOWER(SUBSTRING(name, 2))) as name     -> FROM customers; +-------------------------+ | name                    | +-------------------------+ | Presidents incorporated | | Science corporation     | | Musicians of america    | +-------------------------+ 3 rows in set (0.00 sec)  

NULL and Strings

In the previous lesson, you learned that a NULL is no value and that it is not the same as the number zero. Using NULL in an expression always causes the result to be NULL.

Similar rules apply for strings: NULL is not equal to an empty string. The following example also shows that having a NULL argument to CONCAT() causes NULL to be returned, regardless of the other arguments:

 mysql> SELECT CONCAT('Hello', NULL, 'World'); +--------------------------------+ | CONCAT('Hello', NULL, 'World') | +--------------------------------+ | NULL                           | +--------------------------------+ 1 row in set (0.00 sec)