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
.