Most of the major RDBMSs provide several options for concatenating two or more strings.

• There’s the CONCAT() function, which concatenates its arguments.
• There’s also a CONCAT_WS() that allows you to specify a separator that separates the concatenated strings.
• And there’s also a string concatenation operator, which allows us to concatenate its operands.

Below are examples of each method.

The CONCAT() Function

Most major RDBMSs provide a CONCAT() function for concatenating its string arguments. Generally, non-string arguments are converted to a string before the concatenation occurs.

Example:

SELECT CONCAT('Bangkok', 'Breaking');

Result:

BangkokBreaking

With this function, if we want to include a space between the strings, we’d need to either add a space to one of the strings, or include a space as a separate argument:

SELECT 
    CONCAT('Bangkok ', 'Breaking') AS "Option 1",
    CONCAT('Bangkok', ' ', 'Breaking') AS "Option 2";

Result:

+------------------+------------------+
| Option 1         | Option 2         |
+------------------+------------------+
| Bangkok Breaking | Bangkok Breaking |
+------------------+------------------+

RDBMSs that have a CONCAT() function include MySQL, MariaDB, SQL Server, Oracle, and PostgreSQL.

SQLite doesn’t include a CONCAT() function, but it does have a string concatenation operator (see below).

The CONCAT_WS() Function

Another way to include a space between the strings is to use the CONCAT_WS() function. This function allows you to specify a separator that will be used to separate all concatenated strings.

Example:

SELECT CONCAT_WS(' ', 'Bangkok', 'Breaking');

Result:

Bangkok Breaking

This function can be particularly useful if you have a lot of strings to concatenate:

SELECT CONCAT_WS(', ', 'Red', 'Green', 'Orange', 'Blue');

Result:

Red, Green, Orange, Blue

In such cases, you only need to specify the separator once.

RDBMSs that have a CONCAT_WS() function include MySQL, MariaDB, SQL Server, and PostgreSQL.

SQLite and Oracle don’t have a CONCAT_WS() function, but they do have a string concatenation operator (see below).

The String Concatenation Operator

Most RDBMSs include a string concatenation operator that concatenates its operands.

If you’re using SQLite, this is your only option.

Example:

SELECT 'Spy' || 'craft';

Result:

Spycraft

Note that in MySQL, you will need to enable the pipe concatenation operator first.

The same goes for MariaDB.

SQL Server

The above example won’t work in SQL Server. In SQL Server, use the plus sign (+) string concatenation operator:

SELECT 'Spy' + 'craft';

Result:

Spycraft