How to Append Strings to an Existing Field in MySQL

Mehvish Ashiq Feb 02, 2024
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.

Mehvish Ashiq avatar Mehvish Ashiq avatar

Mehvish Ashiq is a former Java Programmer and a Data Science enthusiast who leverages her expertise to help others to learn and grow by creating interesting, useful, and reader-friendly content in Computer Programming, Data Science, and Technology.

LinkedIn GitHub Facebook

Related Article - MySQL String