Concat Column in MySQL

In the world of relational databases, the need to combine or concatenate values from different columns often arises. This task can be accomplished using the SQL SELECT query along with either the CONCAT or CONCAT_WS function. In this blog post, we’ll explore both methods to join or concat column in MySQL query.

Using CONCAT Function

The CONCAT function is a widely supported method for concatenating columns in SQL. Following is an example of how we can use it:

SELECT CONCAT(firstName, ' ', lastName) AS fullName FROM user;

In this query, firstName and lastName are the columns we want to concatenate, and a space is used as a separator between their values. The result is aliased as fullName for clarity.

Using CONCAT_WS Function

To use CONCAT_WS function, we can use following sample query:

SELECT CONCAT_WS(' ', firstName, lastName) AS fullName FROM user;

This will also work the same as previous example.

Choosing Between CONCAT() and CONCAT_WS()

• Use CONCAT() when:
  • You want to concatenate strings without any specific separator.
    • Example: concat(col1, col2, col3) as concatCol. There is no separator.
  • There is no need for a delimiter between concatenated values. If you want you can add like I’ve shown in using concat function above.
  • This function returns NULL value if the column has value NULL.
• Use CONCAT_WS() when:
  • You want to concatenate strings and specify a separator between them.
  • You need a cleaner way to handle the insertion of a separator without extra logic.
  • This does not return NULL even though there is NULL value in the column.

Frequently Asked Questions (FAQs)