In most cases, concatenating a string and a number in SQL is no different to concatenating two strings.

Most DBMSs will concatenate the string and number as if they were both strings. This is because the number is usually converted to a string before the concatenation operation.

Most major RDBMSs provide a CONCAT() function for concatenation operations, as well as a CONCAT_WS() function that allows you to specify a separator for which to separate the concatenated arguments.

Most major RDBMSs also include a string concatenation operator, which allows us to concatenate its operands.

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('Player', 456);

Result:

Player456

If we want to include a space between the concatenated arguments, we can either add a space to one of the strings, or include a space as a separate argument:

SELECT 
    CONCAT('Player ', 456) AS "Option 1",
    CONCAT('Player', ' ', 456) AS "Option 2";

Result:

+------------+------------+
| Option 1   | Option 2   |
+------------+------------+
| Player 456 | Player 456 |
+------------+------------+

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

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

The CONCAT_WS() Function

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

Example:

SELECT CONCAT_WS(' ', 'Player', 456);

Result:

Player 456 

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

SELECT CONCAT_WS(', ', 'Red', 2, 'Green', 456, 'Black');

Result:

Red, 2, Green, 456, Black

In such cases, you only need to specify the separator once. In this example, I specified a comma and a space as the separator.

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 'Player' || 456;

Result:

Player456

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.

Also, the number will also need to be converted to a string before attempting to concatenate:

SELECT 'Player' + CAST(456 AS varchar(3));

Result:

Player456

Here’s what happens if I try to do it without first converting the number to a string:

SELECT 'Player' + 456;

Result:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Player' to data type int.

SQL Server thinks that we’re trying to add two numbers (because + is also the addition operator) and therefore returns an error, due to one of the operands being a string.