How to Append Strings to an Existing Field in MySQL
Today, we will learn to concatenate or append string values in the MySQL field using the CONCAT() and CONCAT_WS() functions.
Use CONCAT() and CONCAT_WS() to Append Strings to an Existing Field in MySQL
To learn about the CONCAT() and CONCAT_WS(), let’s use the following queries to create a category table containing two fields: category_id and category_code.
Example 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)
Use CONCAT() to Append Strings to an Existing Field in MySQL
Now, we concatenate the word standard with each value in the category_code column by using the CONCAT() method.
Example 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)
The CONCAT() method takes one or multiple string type arguments and concatenates them into one string. This function needs at least one parameter; otherwise, an error would be generated.
If the expression is a numeric value or nonbinary string, then the CONCAT() method returns the binary string or nonbinary string. Similarly, if the expression is a NULL or binary string, the CONCAT() method returns NULL or binary string.
The above output shows that the word standard is appended without any separator. To add a separator, we execute the query in the following manner.
Example 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 that we want to append with a separator, then we will be doing as follows using the CONCAT() method.
Example 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 a string you want to append; the code would not be clean anymore. So, this is where we use the CONCAT_WS() function.
Use CONCAT_WS() to Append Strings to an Existing Field in MySQL
Example 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(), the CONCAT_WS() method also takes string type arguments. The first argument is the separator, and the remaining are the string values we want to append.
This method ignores the expression with a NULL value and returns a NULL value if the separator is NULL.
