JIYIK CN >

In this article, we will learn to use CONCAT()the and CONCAT_WS()functions to concatenate or append string values ​​in MySQL fields.

Use CONCAT() and CONCAT_WS() to append a string to an existing field in MySQL

To understand CONCAT()and CONCAT_WS(), let's use the following query to create a category table with two fields: category_id and category_code.

Sample code:

CREATE TABLE category(
    category_id INT,
    category_code VARCHAR(50)
);

INSERT INTO category (category_id, category_code)
VALUES
(1, 'Windows_1'),
(2, 'Windows_2'),
(3, 'Windows_1');

SELECT * FROM category;

Output:

+-------------+---------------+
| category_id | category_code |
+-------------+---------------+
|           1 | Windows_1     |
|           2 | Windows_2     |
|           3 | Windows_1     |
+-------------+---------------+
3 rows in set (0.00 sec)

Using CONCAT() to append a string to an existing field in MySQL

Now, we use CONCAT()the method to concatenate the word standard with each value in the category_code column.

Sample code:

UPDATE category SET category_code = CONCAT(category_code, 'standard');

Output:

+-------------+-------------------+
| category_id | category_code     |
+-------------+-------------------+
|           1 | Windows_1standard |
|           2 | Windows_2standard |
|           3 | Windows_1standard |
+-------------+-------------------+
3 rows in set (0.02 sec)

CONCAT()Method takes one or more string type arguments and concatenates them into a single string. This function requires at least one argument; otherwise, an error is raised.

If the expression is a numeric value or a non-binary string, CONCAT()the method returns a binary string or a non-binary string. Similarly, if the expression is NULL or a binary string, CONCAT()the method returns NULL or a binary string.

The above output shows that the word standard is appended without any delimiter. To add the delimiter, we execute the query in the following way.

Sample code:

UPDATE category SET category_code = CONCAT(category_code, '_','standard');

Output:

+-------------+--------------------+
| category_id | category_code      |
+-------------+--------------------+
|           1 | Windows_1_standard |
|           2 | Windows_2_standard |
|           3 | Windows_1_standard |
+-------------+--------------------+
3 rows in set (0.00 sec)

Suppose we have a string consisting of 3 words and we want to append a separator after it, then we will use the CONCAT() method to do the following.

Sample code:

UPDATE category SET
category_code = CONCAT(category_code,'_','standard1','_','standard2','_', 'standard3');

Output:

+-------------+-----------------------------------------+
| category_id | category_code                           |
+-------------+-----------------------------------------+
|           1 | Windows_1_standard1_standard2_standard3 |
|           2 | Windows_2_standard1_standard2_standard3 |
|           3 | Windows_1_standard1_standard2_standard3 |
+-------------+-----------------------------------------+
3 rows in set (0.06 sec)

Now, think of 6 words in the string you want to append; the code won't be clean anymore. So, this is CONCAT_WS()where we use the function.

Using CONCAT_WS() to append a string to an existing field in MySQL

Sample code:

UPDATE category SET
category_code = CONCAT_WS('_',category_code,'standard1','standard2','standard3');

Output:

+-------------+-----------------------------------------+
| category_id | category_code                           |
+-------------+-----------------------------------------+
|           1 | Windows_1_standard1_standard2_standard3 |
|           2 | Windows_2_standard1_standard2_standard3 |
|           3 | Windows_1_standard1_standard2_standard3 |
+-------------+-----------------------------------------+
3 rows in set (0.00 sec)

Like CONCAT(), CONCAT_WS()the method also takes string type parameters. The first parameter is the separator and the rest are the string values ​​we want to append.

This method ignores expressions with NULL values ​​and returns a NULL value if the delimiter is NULL.