Concat function in SQL

Definition: 

CONCAT” is a built-in function in SQL Server that is used to concatenate two or more strings into a single string. The syntax of the “CONCAT” function is as follows: 

CONCAT(string1, string2, …., string_n)

For understanding CONCAT () we will create a table and perform some examples using this table.  

Let’s do some examples for understand the concat() function.

Combining two columns: 
 
Example: We will combine the ‘FirstName’ and ‘LastName’ columns into a single column called FullName. The resulting output will be a list of full names. 

Method 1: 

SELECT FIRSTNAME + ‘ ‘ + LASTNAME AS FULLNAME
FROM [HR].[Employees]

Method 2:

SELECT empid,country, region, city,
CONCAT(EMPID, ‘,’ + COUNTRY, ‘,’ + REGION, ‘,’ + CITY) AS LOCATION
FROM [HR].[Employees]

Advantages: 

  • The CONCAT() function is very useful for combining string data from multiple columns or variables into a single string value. This can be especially helpful when constructing queries or reports that require data to be displayed in a specific format. 
  • The function is easy to use and does not require any special knowledge of SQL syntax or functions. 
     
  • The CONCAT() function is built into SQL Server, so it is widely available and compatible with most database management systems. 

Limitations: 

  • The CONCAT() function can only be used with string data types. If you attempt to concatenate non-string data types, you will get an error message. 
     
  • The function does not automatically add any spaces or punctuation between the concatenated strings. If you want to add separators or other characters, you will need to include them as part of the function arguments. 
     
  • The CONCAT() function does not handle NULL values very well. If any of the arguments passed to the function are NULL, the resulting output will be NULL. This can be problematic if you are concatenating data from multiple columns and some of them contain NULL values. 
     
  • The CONCAT() function is less flexible than other string functions like STUFF() or REPLACE(). If you need to perform more complex string manipulation, you may need to use a different function or combination of functions.