String Functions
String FunctionsMySQL includes many functions that can be used to manipulate strings. You learn the most useful functions in this section.
ConcatenationJoining 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
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
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)
Trimming and Padding Strings
Sometimes you will be working with strings that contain excess whitespace characters. To trim space
mysql>
SELECT LTRIM(' MySQL'), RTRIM('MySQL ');
+-------------------+--------------------+
LTRIM(' MySQL') RTRIM('MySQL ')
+-------------------+--------------------+
MySQL MySQL
+-------------------+--------------------+
1 row in set (0.00 sec)
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
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
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 ReplaceThe 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 +-----------------------------+--------------------+--------+ lincoln@presidentsinc.com 8 25 nixon@presidentsinc.com 6 23 fdr@presidentsinc.com 4 21 roosevelt@presidentsinc.com 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
mysql> SELECT email, REPLACE(email, '@', ' at ') -> FROM customer_contacts -> WHERE customer_code = 'PRESINC'; +-----------------------------+--------------------------------+ email REPLACE(email, '@', ' at ') +-----------------------------+--------------------------------+ lincoln@presidentsinc.com lincoln at presidentsinc.com nixon@presidentsinc.com nixon at presidentsinc.com fdr@presidentsinc.com fdr at presidentsinc.com roosevelt@presidentsinc.com roosevelt at presidentsinc.com +-----------------------------+--------------------------------+ 4 rows in set (0.00 sec) Breaking Up a StringTo 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
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)
{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}
SUBSTRING()
is useful when you want to split a string based on fixed character
SUBSTRING_INDEX()
takes a string argument followed by a delimiter character and the number of
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)
Case ConversionTo 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 StringsIn 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)
|
