An Introduction to the Concat Function in SQL

There are many string functions that can be applied on strings in SQL. At times, it’s necessary for a user to join or combine two strings together in a program. Concatenation is useful for joining strings from different sources that include literal strings, column values or output from user-defined functions. This functionality is accomplished with concatenate function in SQL. It can be performed using the command “CONCAT (name of the strings to be concatenated)”.

Learn SQL Programming in just 6 days by taking a course at Udemy.com

A programmer can use the command – CONCAT (Hello,Age,Date) where Hello, Age and Date are strings, the output given is  “goodmorning4028-05-2014”. It might seem illogical to do this in the present scenario but it is very important and useful when making programs.

There are many data types to store data and support programming in SQL and these data types are:

• Exact numeric
• Unicode Character string
• Approximate numeric
• Binary strings
• Date and time
• Character strings
• Other data types

Among them, strings are classified further into the following:

1. Character Strings: They contain char, varchar and text data.
2. Unicode Character Strings: They contain nchar, nvarchar and ntext data.
3. Binary strings: They contain binary, varbinary and image data.

• CONCAT_WS(): This returns a concatenated string with a separator
• CONCAT(): It returns the concatenated string.

Examples of concatenation:

1. Using CONCAT

SELECT CONCAT (‘Good ', 'Morning ', 28, '/', ‘05’) AS Result;

Here is the result set.

Result

————————-

Good Morning 28/05

(1 row(s) affected)

2. Using CONCAT along with NULL values

CREATE TABLE user (
user_name nvarchar(300) NOT NULL,
user_age nvarchar(300) NULL,
user_language nvarchar(300) NOT NULL
);
INSERT INTO user VALUES( 'Robert', NULL, 'English' );
SELECT CONCAT (user_name, user_age, user_language) AS Result
FROM user;

Here is the result set.

Result

——————

Robert English

(1 row(s) affected)

The CONCAT function appends one string to the end of another string. In addition, the CONCAT function also gives us the flexibility to concatenate strings as well as numeric values.  However, the syntax depends on the database system (Microsoft SQL Server, MySQL, Oracle, Microsoft Access) used.

Let’s talk about Microsoft SQL Server. Earlier, “SQL Server 2012”  ‘+’ (plus sign) was used as an operator for concatenating two strings or integers into one expression.

Learn SQL Programming by taking a course at Udemy.com

Using plus sign (+) syntax:

The general syntax for concatenating two strings using plus sign:

SELECT ‘string1’ + ‘‘ + ‘string2’ AS Resultset

Example:

Let’s consider a table of name “person_info” which contains the columns as “FirstName”,”LastName” and “FullName”.

SELECT [‘FullName’] + ‘ ’ +[‘LastName’] AS [FullName]

Now, the table will look like:

Concatenate with NULL:

What if the Null value is present in any of the columns of the table? In the older versions of “SQL  Server 2012”, the ISNULL function was used for converting NULL to an empty string.

Example:

If the “person_info” table contains one or more columns with the name “MiddleName” that are empty, you will concatenate all three columns without using ISNULL.

SELECT TOP 1 [FirstName],[MiddleName],[LastName]
, [FirstName] + [MiddleName] + [LastName] as [FullName]
FROM [person_info]

The above example will not be able to convert the NULL into an empty string because “ISNULL” is not present in the code and will show NULL in FullName field as shown below.

And if ISNULL is used then the code will look like:

SELECT ISNULL([FirstName],’ ’) + ‘ ‘ + ISNULL([MiddleName],’ ’) + ‘ ‘ + ISNULL([LastName],’ ’) AS [FullName]
FROM [person_info]

The above example will convert NULL into an empty string and concatenate the string as “Michael Jackson”.

Using CONCAT function in SQL 2012:

“SQL Server 2012” has introduced a very handy and important function for concatenating strings, numeric or integer values. This function is CONCAT().

General syntax:

CONCAT  ( String1,String2,….StringN)

Example 1: Concatenating strings

In this example, we are going to concatenate four strings values using CONCAT function. Strings are ‘My’,’name’,’is’ and ‘Jack’

SELECT CONCAT (‘My ’,’name ’,’is ’,’Jack’) AS Result

Result:

My name is Jack

Example 2: Concatenating integers

In this example, we will concatenate integer values by implicitly converting integer data into a string data type using the CONCAT function. Let’s concatenate the numbers 2,4 and 35.

SELECT CONCAT (2,4,35) AS Result

Result:

2435

Example 3: Concatenating numeric values

In this example we will concatenate the numeric values by implicitly converting a numeric datatype into string datatype using CONCAT function. Let’s concatenate 23.45,78.21 and 87.91

SELECT CONCAT (23.45,78.21,87.91) AS Result

Result:

23.4578.2187.91

Example 4: Concatenating less than two strings

If you use only one string in the argument section of CONCAT function,

SELECT CONCAT (‘Ben’) AS Result

The above code will show an error message “CONCAT function requires 2 to 254 arguments” because it’s necessary to give two or more strings in the argument section of the CONCAT function.

Example 5: Concatenating NULL value passed from the database

What happens if the NULL value is present in the database table? Let’s take our “person_info” table with two rows for our example.

The “MiddleName” column of this table contains NULL values.

SELECT CONCAT ([FirstName] , [MiddleName] , [LastName]) as [FullName]
FROM [person_info]

Now the above table will look like:

The CONCAT function will convert the NULL into the empty string and produce the result as shown in the above column “FullName”. Unlike the “+” sign syntax the CONCAT function does not require ISNULL for converting NULL into an empty string.

Learn SQL programming from scratch at Udemy.com

Conclusion:

Concatenation generally produces more readable output while keeping data in separate columns for more flexibility. However, just understanding the concept of concatenating strings or integers is not enough since it is important to know when and where to use it. The syntax should be checked according to the database system which is being used.