SQL Server: Concatenate Multiple String Values

There are at least 2 ways to concatenate 2 or more string values, That's by using the plus sign (+) or the CONCAT function. 

1. Concatenate by using the plus sign (+)

SELECT 'Rani' + 'Irsan'

2.  Concatenate by CONCAT function

SELECT CONCAT('Rani', 'Irsan')

What's the difference? Let's try and check the result.

The result is the same for both. It will be different when we try to concatenate 2 or more different data types. Concatenating with "+" will generate an error.

Otherwise if we're using CONCAT function, the result will be as below: 

Actually, it is possible to still use plus sign (+). But we have to change the data type first. We can use CAST or CONVERT to change it as below:

SELECT 'Rani' + CAST(1 AS VARCHAR(1))

SELECT 'Tanggal Hari ini ' + CAST(GETDATE() AS VARCHAR(20))

Data conversion using CAST / CONVERT is also needed for concatenate 2 or more numeric and date data using (+), otherwise it will will produce a sum.

Let see the differences.