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