How to connect strings with MySQL CONCAT

The MySQL CONCAT command lets you combine two or more strings into one unit. While MySQL CONCAT doesn’t allow any separators, you can use MySQL CONCAT_WS to combine and output information even more clearly.

The relational database management system MySQL works with tables. Data is clearly stored in columns and rows and can be retrieved as needed and linked. This is important when information from two or more tables is needed. The handy command to merge multiple columns is MySQL CONCAT. You use this string function when you want to concatenate two or more expressions and combine and output them in a single string. There are several ways to use MySQL CONCAT.

Since MySQL stores data in different tables and in different columns and rows, data is kept separate. But for certain queries or tasks it may be necessary to output this information together. Below, we’ll show you some examples using the combination of last name, first name, and address. These details are initially kept in individual columns and can also be searched for or output individually. But if you want to combine all three, MySQL CONCAT will help you.

The basic syntax of MySQL CONCAT is the same as for the commands MySQL DELETE and MySQL REPLACE. After the command, all expressions that are to be merged are listed and separated by commas. It looks something like this:

It’s important to note that MySQL CONCAT converts expressions with a numeric value into a binary string. Binary strings remain binary strings, and non-binary strings also remain non-binary. And an expression with a value of 0 retains a value of 0.

As an alternative to the regular MySQL CONCAT command, there is the MySQL CONCAT_WS function. The second option works with a separator between the strings, while MySQL CONCAT doesn’t allow the use of separators. The basic syntax of MySQL CONCAT_WS looks like this:

MySQL CONCAT_WS returns the value 0 only if the value of the delimiter is 0.

To understand how MySQL CONCAT works, it’s worth looking at a few simple examples. First, we’ll simply join two strings together. Here’s how:

In this case you’ll notice that the output is a simple “MySQLCONCAT”. If you now insert a null value, the command reads like this:

While this first example doesn’t offer any added value and only illustrates the basic functionality, the next attempt offers more practical benefits. For this, we’ll use a customer list. This is divided into various columns, including customer number, first name, last name, address, or phone number. The data is stored separately. But MySQL CONCAT can help you create a common output. For a listing of customers with complete names, let MySQL concatenate first name, space, and last name. Use the following command:

In the output, all customers are now listed with their first name and last name.

Let’s stick with the above example. To list the names in reverse order and separate last name and first name with a comma, you need the MySQL CONCAT_WS command. The process is similar, but the comma is prepended to the rest of the information. Here’s an example:

The matching output is then “Doe, John”.

The command is ideal to clearly display information. In the last example you can see how to output a complete address with MySQL CONCAT_WS. This is how the matching code looks like:

The output looks like this: