MySQL string functions

As we’ve mentioned in the previous article, the MySQL string functions enable you to manipulate strings of data. In this article we will describe some common string functions. We will use our old table testtb for the examples in this chapter:

mysql> SELECT * FROM testtb;
 +-------+-------------+------+
 | name | surname | year |
 +-------+-------------+------+
 | Amy | Bryant | 1991 |
 | Mark | Smith | 1955 |
 | John | von Neumann | 1921 |
 | Aaron | Rogers | 1995 |
 | Brian | Cormier | 1988 |
 +-------+-------------+------+
 5 rows in set (0.00 sec)

CONCAT function

The CONCAT function is used to concatenate two or more strings together. For example, to return the full names of contacts, we will use the CONCAT function to concatenate the name and surname columns:

mysql> SELECT CONCAT (name,' ',surname) FROM testtb;
 +---------------------------+
 | CONCAT (name,' ',surname) |
 +---------------------------+
 | Amy Bryant |
 | Mark Smith |
 | John von Neumann |
 | Aaron Rogers |
 | Brian Cormier |
 +---------------------------+
 5 rows in set (0.01 sec)

LENGTH function

The LENGTH function returns the length of a string in bytes. To get the actual number of characters in a string, we can use the CHAR_LENGTH function.

For example, to get the character length of the records in the column name, we would use the following command:

mysql> SELECT CHAR_LENGTH (name) FROM testtb;
 +--------------------+
 | CHAR_LENGTH (name) |
 +--------------------+
 | 3 |
 | 4 |
 | 4 |
 | 5 |
 | 5 |
 +--------------------+
 5 rows in set (0.00 sec)

REPLACE function

To replace a string in a column of a table by a new string, you can use the REPLACE function. The syntax:

REPLACE(str,old_string,new_string);

As you can see from the syntax above, the REPLACE function accepts three parameters: it replaces the old_string by the new_string in the str.

Here is an example. To replace a misspelled word webseite in the string About this webseite, we would use the following command:

mysql> SELECT REPLACE ('About this webseite','webseite','website');
 +------------------------------------------------------+
 | REPLACE ('About this webseite','webseite','website') |
 +------------------------------------------------------+
 | About this website |
 +------------------------------------------------------+
 1 row in set (0.00 sec)