Concat SQL Function Explained [Practical Examples]

Overview of Concat SQL Function

Concat SQL is one of the most useful string functions of SQL Server, which is used to join or to concatenate two or more strings together in an end-to-end manner and returns the resulting string

In a Database management system to store text values we need to declare columns with string datatypes SQL server provides various string datatypes to store text-base values in columns, SQL Server has the following data types for storing data:

• char(n): n defines the character string length from 0 to 8000 characters
• varchar(n): n defines the character string length from 0 to 8000 characters
• varchar(max): Maximum storage of 2^31-1 bytes (2 GB)
• nchar(n): Character string length in byte-pairs (0 to 4000 characters)
• nvarchar: Character string length in byte-pairs (0 to 4000 characters)

 

SQL Concatenate Strings

The easiest way to concatenate strings is to use the SQL standard string concatenation operator (||):

String_expression1 || String_expression2

SQL Server does not support the ISO SQL string concatenation operator, use a + instead:

String_expression1 + String_expression2

MySQL supports neither || nor + for concatenating strings, but it does support an unlimited number of string arguments

 

Concat() SQL function Syntax

CONCATE (String_expression1 | column_name1, string_expression2 | column_name2 [, string_expressionN | column_nameN,….])

A string expression or a column_name holds a string value to concatenate to the other string value. The CONCAT function requires at least two string value arguments and no more than 254 string value arguments.

 

Concat() SQL Return Types

SQL Concat() function will return a string expression which length and type depend on the input argument given with function

 

Concat() SQL  function features:

• It requires at least two arguments to perform concatenation otherwise it will raise an error
• It implicitly converts all arguments to string types before concatenation
• It function implicitly converts null values to empty strings
• If NULL value is given as an argument in Concat function n, it will return an empty string of type varchar (1)

 

Example-1: SQL Concatenate two strings

Simple example with string expression

To join two string expression using Concat SQL function

SELECT  {fn CONCAT('Hello', 'Good morning') } AS Result

• In the above query , Concat() function is applied on two string expressions “Hello” and “Good Morning”
• The resulting string will be a merge string of both arguments “HelloGood morning”

OUTPUT:

 

Example-1: SQL Concatenate four strings

Join four-string expressions using Concat SQL function

SELECT {fn CONCAT('SQL', 'Concat ', 'Function', 'example') } AS Result

In the above query, we have applied SQL Concat() function to marge four-string expressions and resulting in one string expression

 

Example-3: SQL Concatenate numerical expressions

To join numerical expression using SQL concatenate function we need to cast numeric value into string type using cast function

SELECT {fn CONCAT(CAST(10 AS VARCHAR(10)), CAST(20 AS VARCHAR(10))) } AS Result

• In the above SQL query, we use Concat SQL function to join integer type expressions 10 and 20
• Before giving numeric expression as an argument in function, we need to convert it into string type using any CAST or CONVERT function

OUTPUT:

 

Concat SQL function with ‘+’ operator

‘+’ operator is used as a concatenation operator to concatenate data together, working of + operator is same the function but difference is that with SQL Concat() function we need to cast the numerical expression to perform concatenation whereas with ‘+’ operator we do not need to perform

SELECT 'Hello' + 'world' AS Result

In the above query, we concat two string expressions using the ‘+' operator

OUTPUT:

 

In below example we used ‘+’ operator with Concat SQL function to join string values and then use as an argument to Concat function

SELECT {fn CONCAT('SQL' + '  ', 'Functions') } AS Result

• In the above query, SQL’+’ operator is used in first argument of Concat function to concatenate string with space within an argument of function
• Resulting string of  the first argument is concat with second argument string expression of the function

OUTPUT:

 

Nested Concat SQL function example

In oracle and in SQL Server the CONCAT function will only allow you to concatenate two values together, to concat more than two values we can use nested Concat SQL function,

In nested SQL concatenate function the  function itself is used as an argument in function, so the resulting string of inner Concat function will be use as argument for outer Concat() function to combine or to join

 

Nested Concat SQL function to concatenate four-string expressions

SELECT  {fn CONCAT('SQL', { fn CONCAT('Concat ', { fn CONCAT('Function', 'example') }) }) } AS Result

• In the above query, three nested Concat SQL function is used, first most inner concat function is executed so connect function and example and resulting string will be used the second argument of the second SQL concatenate function same as the result of second nested concat function will be used as the second argument of outer concat function
• The resulting string value will be a concatenation of all four strings

OUTPUT:

 

Nested Concat SQL function to concatenate string, numeric and special character expression

SELECT  {fn CONCAT('SQL', { fn CONCAT('Tutorial Series ', { fn CONCAT(CAST(11 AS varchar(4)), { fn CONCAT('/', '25') }) }) }) } AS Result

• In the above query, to concat four values, three layers of Concat SQL function is applied
• First, most inner Concat SQL function is executed and the resulting string expression of it will be used as the argument of the next outer function
• Here in this example, numerical expression is also used as an argument so SQL CAST function is applied on numerical data to convert to string type

OUTPUT:

 

Concat SQL function example to Concatenate Single Quotes

String expression in the argument of Concat SQL function is given in single quotation but when we want to add a single quotation mark character within the resulting string of concat function, we need to use 2 additional single quotes within the surrounding single quotation marks to represent a single quote in our result

SELECT  {fn CONCAT('Let ', { fn CONCAT('''', 's learn SQL Server') }) } AS 'Example of Concat function'

• In the above query, to show the single quotation work after one string argument, we give the second argument as two times single quotation
• In this query we nested the Concat SQL function so the first inner concat function is executed then resulting string of nested Concat SQL function will be used as the second argument of outer Concat() function

OUTPUT:

 

Concat SQL function with NULL value

When a value does not exist, we represent it as the NULL in SQL server, in Concat SQL function if we give NULL as an argument, it converts it to empty string and it will return NULL as the resulting string

Use NULL as the second argument in Concat SQL function

SELECT  {fn CONCAT('SQL', NULL) } AS Result

• In the above query, two-argument is given with Concat() function, one is string expression and the second is the NULL
• If we give NULL ads the argument in the Concat() function, it will result in NULL as the resulting value

OUTPUT:

 

Concat SQL function with column name as argument

SQL Concatenate function can be applied on SQL table data to perform string operation or to manipulate string data, the function is used with SQL select statement to retrieve table data by merging data value of two or more columns

Consider the patient table of the hospital database for examples
Patient Table

Use SQL Concat function to concatenate patient name with address

SELECT   {fn CONCAT(name, address) } AS 'Patient Details'
FROM   patient

• In the above query two columns of the patient table is given as the argument
• SQL Concatenate function will combine both column values for all records of the table

OUTPUT:

 

Difference between Concat SQL and Concat_ws SQL

Concat  SQL and Concat_ws SQL both functions are used to do concatenation on string expression, Concat_ws function is one of the SQL built-in string manipulation functions but SQL server and oracle do not support this function, it works on MySQL platform

• CONCAT_WS() function can do the concatenation along with a separator between strings, whereas in CONCAT() function there is no concept of the separator
• CONCAT() function returns NULL if any of the arguments is NULL, whereas CONCAT_WS() function returns NULL if the separator is NULL

Examples:

Use SQL Concatenate function to combine the patient name with diseases  from the patient table

SELECT {fn CONCAT(name, disease) } AS 'Patient Disease'
FROM patient

OUTPUT:

 

SELECT {fn CONCAT_WS(name, disease) } AS 'Patient Disease'
FROM patient

OUTPUT:

• In above both query we applied same argument name and dieses in both the function concat and concat_ws it output shows difference
• Concat_ws function place separator between value of both arguments but concat function do not

 

Summary

In this article , Concat SQL function has been covered with introduction and practical examples, in first section of this article Overview of SQL concat function and its syntax with return types has been explained, in further section different type of examples like concatenate string expression, numerical expression, Null value as an argument and shows single quotation in resulting concated string as well as how to put space as the argument in concat function is covered , in last section concatenation has perform on table data by giving column name as the argument and difference between SQL concat and SQL concat_ws has been explained with examples

 

References

SQL string function

 

Further Reading

CONCAT SQL