When we need to concatenate two columns simply we can use +
sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's say we have a customers table with following structure and data
Now suppose we have to bind a drop down on page so we need Customer id and FirstName + LastName so we will use
SELECT [CustomerID],
[FirstName] + ' ' + [LastName] AS [CustomerName]
FROM Customers
It will work smooth because first name and last name both have some values no NULL column.
Now let's say, we have to use FirstName, MidleName and LastName as CustomerName, change our query to this one
SELECT [CustomerID],
[FirstName] + ' ' + [MidleName] + ' ' + [LastName] AS [CustomerName]
FROM Customers
It will return
CustomerID CustomerName
----------- ------------------
10001 Jamey Jo Durham
10002 NULL
10003 NULL
10004 NULL
Is there any mistake we made in our sql statement, no. It is because of the null value in Middle name, so how to fix it? We will check the column value by using ISNULL
function and if it is null then we will use blank ('') [we don't want to go here in detail about the differences between blank and null because we are discussing here about concatenation of columns].
SELECT [CustomerID],
IsNull([FirstName], '') + ' ' + IsNull([MidleName], '')
+ ' ' + IsNull([LastName], '') AS [CustomerName]
FROM Customers
/* --- Output ---- */
CustomerID CustomerName
----------- -----------------
10001 Jamey Jo Durham
10002 John Smith
10003 Abhay Singh
10004 Sunil Kumar
It looks good but there is a problem, if you will see closely record 2,3 and 4, there are double spaces between first name and last name. It is not so bad, but suppose if we need ot use , in place of space then it will not look good. check the following example.
SELECT [CustomerID],
[FirstName] + ' ' + [MidleName] + ' ' + [LastName] AS [CustomerName],
[Address1] + ', ' + [Address2] + ', '+ [City]
+ ', '+ [State] + ', ' + [Country] + '-' + [Zip] AS [Address]
FROM Customers
/* ----------- See Output ----------- */
CustomerID CustomerName Address
----------- ---------------- ----------------------------------------------
10001 Jamey Jo Durham 127 Park Avenue, Lane 10, Jersey City, NJ, USA-10234
10002 NULL Lane 2, Friends Colony, New Delhi, DL, India-110025
10003 NULL NULL
10004 NULL NULL
Now let's use ISNULL
function to check the null value and use blank if value is null, so new sql query will be
SELECT [CustomerID],
IsNull([FirstName], '') + ' ' + IsNull([MidleName], '') + ' '
+ IsNull([LastName], '') AS [CustomerName],
IsNull([Address1], '') + ', ' + IsNull([Address2], '') + ', '
+ IsNull([City], '') + ', '+ IsNull([State], '') + ', '
+ IsNull([Country], '') + '-' + IsNull([Zip], '') AS [Address]
/* --- Output ----*/
CustomerID CustomerName Address
----------- ---------------- ----------------------------------------------------
10001 Jamey Jo Durham 127 Park Avenue, Lane 10, Jersey City, NJ, USA-10234
10002 John Smith Lane 2, Friends Colony, New Delhi, DL, India-110025
10003 Abhay Singh A-123, T. Appartment, , , MB, -220345
10004 Sunil Kumar A-92 C, , New Delhi, , -
FROM Customers
In record 3 and 4 CustomerName have double space one for MiddleName and one for LastName.
Similarly, Address column have extra commas, so how to fix them, do we need some logic to first check and then use space or commas. No, we will use Stuff
and Coalesce
to fix this problem, so change our above query to this one
SELECT [CustomerID],
Stuff(
Coalesce('' + [FirstName], '')
+ Coalesce(' ' + [MidleName], '')
+ Coalesce(' ' +[LastName], '')
, 1, 1, '') AS [CustomerName],
Stuff(
Coalesce(', ' + [Address1], '')
+ Coalesce(', ' + [Address2], '')
+ Coalesce(', ' + [City], '')
+ Coalesce(', ' + [State], '')
+ Coalesce(', ' + [Country], '')
+Coalesce('-' + [Zip], '')
, 1, 1, '') AS [Address]
FROM Customers
And here is the final output, is not it what we want
![]() |
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
By Ali Adravi On 13 Feb, 13 Viewed: 12,778 |
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 1,096
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 936
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,394
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 678
TRY…CATCH constructs do not trap the following conditions: - Warnings or informational messages that have a severity of 10 or lower. - Errors that have a severity of 20 or higher those stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of... By Ali Adravi On 28 Dec 2012 Viewed: 586