Using CONCAT in SQL

In my line of work, we regularly put different fields together for representation to our customers.  There are two ways we can approach this in SQL:

• Concatenate the fields together using the "+" sign
• Using the CONCAT() function

Both of these approaches give the same results in most cases, so why should you use one solution over another?  I would first say because of how it looks.  For example, if we want to concatenate the first name and last name together, here is what our two solutions would look like:

Using "+" sign:

SELECT FirstName + ' ' + LastName from Customers

Using CONCAT() function:

SELECT CONCAT(FirstName, ' ', LastName) from Customers

This is a pretty simplistic example.  If we use something with more fields, it may become more evident.  For example, let's say that we have three different fields for a customer's phone number, we could put the three fields together like so:

Using "+" sign:

SELECT '(' + AreaCode + ')' + CityCode + '-' + Suffix from Customers

Or

Using CONCAT() function:

SELECT CONCAT( '(', AreaCode, ')', CityCode, '-', Suffix) from Customers

Another reason to use the CONCAT() function is that integer values are automatically converted to string.  If, for example, you put numbers together when using your "+" sign, SQL will add the two values together instead of concatenating them:

This returns the value of 3:

SELECT 1 + 2

This returns the value of 12:

SELECT CONCAT(1, 2)

In the first example, you will need to make sure you are converting integer values to a string, which could be quite cumbersome.

To read more on the CONCAT() function, go here: SQL CONCAT