August 4, 2009 at 7:06 am
I have Select that I concatenate the customer name.
SELECT LastName + ', ' + FirstName+ ', ' + MiddleInit AS Name
When the query does a Name with a NULL middle initial it doesn't populate the name.
If I have:
Smith, John, A
Smith, Mike, B
Smith, Andy
The Smith, Andy will be blank.
How can I correct my Select statement?
Thank you for your time.
August 4, 2009 at 7:23 am
Simplest way is
SELECT LastName + ', ' + FirstName+ ', ' + COALESCE(MiddleInit,'') AS Name
August 4, 2009 at 7:59 am
Thank you, sir!
August 4, 2009 at 10:16 am
--
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
August 4, 2009 at 11:12 am
I appreciate that added info. 😎
I was just thinking about tackling that.
August 4, 2009 at 12:11 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply