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 — Michael Berkowski
– Michael Berkowski, Commented Mar 7, 2014 at 16:33

b.runyon · Accepted Answer · 2014-03-07 16:35:55Z

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 7, 2014 at 16:35

answered Mar 7, 2014 at 16:33

b.runyon

1547 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Fuck StackOverflow Over a year ago

+1 for using COALESCE, which complies with the ANSI standard.

Sir Crispalot · Accepted Answer · 2014-03-07 16:34:13Z

1

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.

answered Mar 7, 2014 at 16:34

Sir Crispalot

4,8742 gold badges42 silver badges66 bronze badges

Comments

M.Ali · Accepted Answer · 2014-03-07 16:36:24Z

0

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.

answered Mar 7, 2014 at 16:36

M.Ali

69.9k17 gold badges110 silver badges133 bronze badges

Comments

Rahul · Accepted Answer · 2014-03-07 16:40:51Z

0

Probably like this

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

answered Mar 7, 2014 at 16:40

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

1 Comment

msi77 Over a year ago

You need not replace NULLs using CONCAT function. Try this select concat(null,'abc',null).

Collectives™ on Stack Overflow

SQL Select Query returns NULL when appending string

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related