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 sep­ar­at­ors, you can use MySQL CONCAT_WS to combine and output in­form­a­tion even more clearly.

The re­la­tion­al database man­age­ment 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 in­form­a­tion 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 con­cat­en­ate two or more ex­pres­sions 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 in­form­a­tion together. Below, we’ll show you some examples using the com­bin­a­tion of last name, first name, and address. These details are initially kept in in­di­vidu­al columns and can also be searched for or output in­di­vidu­ally. 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 ex­pres­sions 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 ex­pres­sions with a numeric value into a binary string. Binary strings remain binary strings, and non-binary strings also remain non-binary. And an ex­pres­sion with a value of 0 retains a value of 0.

As an al­tern­at­ive 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 sep­ar­at­ors. 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 un­der­stand 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 `MySQL­CON­CAT´. If you now insert a null value, the command reads like this:

While this first example doesn’t offer any added value and only il­lus­trates the basic func­tion­al­ity, 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 sep­ar­ately. But MySQL CONCAT can help you create a common output. For a listing of customers with complete names, let MySQL con­cat­en­ate 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 in­form­a­tion. Here’s an example:

The matching output is then `Doe, John´.

The command is ideal to clearly display in­form­a­tion. 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: