Mysql combine two columns into one column

sql

concatenation

string-manipulation

sql-best-practices

byAnton Shumikhin·Nov 17, 2024

Combine two MySQL columns with CONCAT():

SELECT CONCAT(column1, column2) AS new_column FROM table1;

For adding a separator, use the CONCAT_WS() function:

SELECT CONCAT_WS(' ', column1, column2) AS new_column FROM table1;

Avoid the + operator, it was designed for math, not letters, and may cause unexpected conversions. Stick to CONCAT() for straightforward concatenation or CONCAT_WS() to specify a separator.

For a little twist, here is a nested CONCAT() use-case:

SELECT CONCAT_WS('-', column1, CONCAT(':', column2, column3)) AS new_column FROM table1;

Concatenation operators

When working with strings, some operators can lead you down the rabbit hole. For example, the + operator is intended for numerical addition not for string concatenation. To avoid falling into the silent conversion trap where MySQL could interpret your strings as numbers, always go for CONCAT() or CONCAT_WS().

Similarly, || operator could end up performing a logical OR unless the PIPES_AS_CONCAT SQL mode is enabled. As the saying goes, "it's better to be safe than sorry", thus always make sure you refer to MySQL's official documentation before using other functions or operators.

Dealing with null values

Along the journey of concatenation, you might stumble upon NULL values. In such cases, CONCAT() will return NULL if one of the columns is NULL, while CONCAT_WS() will simply skip NULL values:

SELECT CONCAT_WS('-', column1, column2) AS new_column FROM table1;
-- The dash won't bring along any unwelcomed NULL guests to the party

If you want more control, or have a backup plan in case of NULL values, consider COALESCE() to set a default value:

SELECT CONCAT(COALESCE(column1, 'default'), COALESCE(column2, 'default')) AS new_column FROM table1;
-- A knight in shining armor, COALESCE won't let NULL hurt your result!

Best practices and caveats

When working with concatenation, here are some tips to ensure you create an "SQL masterpiece":

Test-test-test! Make sure you know what CONCAT() and CONCAT_WS() functions are doing and always check for unexpected results especially when dealing with various data types or NULL values.
Double-check your syntax and column names, it's not a spelling bee, but it can ruin your day.
Different SQL databases have their own syntax for string concatenation, make sure you are using the correct one.
Query not working as expected? No worries. Just like in any good movie, the hero always finds a way out. Keep testing and tweaking!

Beyond simple concatenation

Concatenation is not just about joining two fields, you can format dates, generate URLs, or create composite keys. Here's how to build a formatted address:

SELECT CONCAT_WS(', ', street, city, CONCAT(' ZIP:', zipcode)) AS address FROM users;
-- Et Voila! You just created your own GPS with SQL!

Doing so allows us to have a single string representing a full address with a clear indication of the ZIP code.

explain-codes / Sql / Mysql combine two columns into one column

Linked

Mysql CONCAT returns NULL if any field contain NULL



Mysql SELECT AS combine two columns into one



Sql UPDATE all values in a field with appended string CONCAT not working



What does SQL Select symbol || mean?



Can I concatenate multiple MySQL rows into one field?



Mysql, better to insert NULL or empty string?



Mysql Results as comma-separated list



Concatenation operators Dealing with null values Best practices and caveats Beyond simple concatenation