Concatenate and Null value in Select

Question

Post reply

Concatenate and Null value in Select

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ian Scarlett SSC-Insane Points: 23197 More actions · Answer 1

Simplest way is

SELECT LastName + ', ' + FirstName+ ', ' + COALESCE(MiddleInit,'') AS Name

rothj Hall of Fame Points: 3531 More actions · Answer 2

rothj

Hall of Fame

Points: 3531

August 4, 2009 at 7:59 am

#1034255

Thank you, sir!

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 3

--

There is also a trick to handling spaces, commas, or other separators, which is shown in the examples below. The trick is to test an expression which includes the nullable column and the separator. If the column is null, then the expression will be null.

--

declare @name table(firstName varchar(30), middleName varchar(30), lastName varchar(30))

insert into @name

select 'John', 'Q.', 'Public' union all

select 'John', null, 'Smith' union all

select null, 'JOHNCORP', null

--

select ISNULL(firstName+' ','')+ISNULL(middleName+' ','')+ISNULL(lastName,'')

from @name

--

select ISNULL(lastName+', ','')+ISNULL(firstName+' ','')+ ISNULL(middleName,'')

from @name

--

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

rothj Hall of Fame Points: 3531 More actions · Answer 4

I appreciate that added info. 😎

I was just thinking about tackling that.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

You're welcome. I figured you'd get there eventually 😉

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills