SQL Select Query returns NULL when appending string

Question

In a table, there 3 columns: GivenName, FamilyName, MiddleName. And I have to append all three columns values to output a single column like this

Select Upper(GivenName) + FamilyName + Upper(MiddleName) as PersonName.....

But if value for any one of the column is null then the whole output is Null.

Any way if I can check if any of the column is null before appending? So that it is not appended and others which are not null gets appended.

But I cannot use 'where GivenName is not null, FamilyName is not null' condition.

I just dont want to append the string which is null. For Ex:

If GivenName = 'Mark', 
   FamilyName = 'Joseph',
   MiddleName is null

Then output should be : MARK Joseph instead of NULL which has not appended MiddleName as it is Null.

(But in SQL it the output is NULL. Try this..

declare @FirstName nvarchar(20);
declare @GivenName nvarchar(20);
declare @MiddleName nvarchar(20);
set @FirstName = 'Steve';
set @GivenName = 'Hudson';
set @MiddleName = null;

select Upper(@FirstName) + @GivenName + UPPER(@MiddleName) => Outputs Null )

possible duplicate of [SQL Server String Concatenation with Null](http://stackoverflow.com/questions/2916791/sql-server-string-concatenation-with-null) — Michael Berkowski, Mar 07 '14 at 16:33

b.runyon · Answer 1 · 2014-03-07T16:35:55.027

2

Do this:

Select COALESCE(Upper(GivenName), '') + COALESCE(FamilyName, ' ') + COALESCE(Upper
(MiddleName), '') as PersonName

COALESCE will do a null check on the first parameter. If it is null it returns the second parameter

edited Mar 07 '14 at 16:35

answered Mar 07 '14 at 16:33

b.runyon

144
5

+1 for using COALESCE, which complies with the ANSI standard. – Dave Mason Mar 07 '14 at 16:42

score 1 · Accepted Answer · answered Mar 07 '14 at 16:34

You can use the isnull function to simply output an empty string if the column is null:

Select Upper(isnull(GivenName, '')) + isnull(FamilyName,'') + Upper(isnull(MiddleName,'')) as PersonName

I'm not entirely sure you should be doing this in the database ... it seems like a presentation layer concern to me.

score 0 · Answer 3 · answered Mar 07 '14 at 16:36

An way of doing this kind on concatenation is

SELECT ISNULL(FirstName + ' ', '') 
               + ISNULL(MiddleName + ' ', '') 
                          + ISNULL(LastName, '')

And obviously you can use the UPPER and LOWER function where appropriate.

score 0 · Answer 4 · answered Mar 07 '14 at 16:40

0

Probably like this

SELECT CONCAT(ISNULL(FirstName, ''),ISNULL(MiddleName, ''),ISNULL(LastName, ''))
AS FullName FROM testtable

answered Mar 07 '14 at 16:40

Rahul

76,197
13
71
125

You need not replace NULLs using CONCAT function. Try this select concat(null,'abc',null). – msi77 Mar 08 '14 at 13:34

SQL Select Query returns NULL when appending string

4 Answers4