Concatenating Columns in SELECT Problem

kevorkian · June 12th, 2008, 05:47 PM

Ok, I am bothered that I don't know the answer to this - but here is my dilema.

I am trying to combine last name, first name, and middle initial into a column named "Customer Name" in my query. Here is the query in question:

SELECT [customer_record], [mail_date], ([last_name] + ', ' + [first_name] + ' ' + [middle_init]) as [Customer Name], [email], [phone] FROM [customers] WHERE ([dealer_code] = @dealer_code) ORDER BY [mail_date]

The issue I am having is that when the middile initial is NULL, which is often - the entire Customer Name column is null, even though there are values in the first and last name.

Any ideas? (I know I am going to kick myself as soon as I find the answer)

Old Pedant · June 12th, 2008, 05:59 PM

Good old CASE WHEN:

Code:

SELECT customer_record, mail_date, 
       (last_name + ', ' + first_name + CASE WHEN middle_init IS NULL THEN '' ELSE ' ' + middle_init END) as Customer Name, 
      email, phone
FROM customers WHERE dealer_code = @dealer_code
ORDER BY mail_date

I got rid of your extraneous ( ) and [ ] ... no reason for any of the ones I omitted.

kevorkian · June 12th, 2008, 06:41 PM

oh good lord - thanks!!!

I normally don't use the brackets but I copies this directly from a .net web form and that is how it was generated.

Appreciate it as always!

Old Pedant · June 12th, 2008, 07:08 PM

You know, if you don't care about an unnecessary trailing space, you can do this even easier:

Code:

SELECT customer_record, mail_date, 
       (last_name + ', ' + first_name + ' ' + ISNULL(middle_init,'') ) as Customer Name, 
      email, phone
FROM customers WHERE dealer_code = @dealer_code
ORDER BY mail_date

So if middle_init is indeed null, then you'll have a trailing space. But if you are showing this in typical HTML display, or printing it on paper, nobody can see the space, so what does it matter?

Jeff Moden · June 12th, 2008, 07:23 PM

No need for trailing space... anything plus NULL is still NULL...

SELECT customer_record, mail_date,
(last_name + ', ' + first_name + ISNULL(' ' + middle_init,'') ) as Customer Name,
email, phone
FROM customers WHERE dealer_code = @dealer_code
ORDER BY mail_date

--Jeff Moden

Old Pedant · June 13th, 2008, 01:08 PM

DOH! OF COURSE!

I sure overcomplicated it!

Funny part is that I have preached on NULL not being same as EQUAL or NOT-EQUAL many times. Why didn't I tumble to adding to null being same? 20 lashes with a wet SQL Server manual for me!

Jeff Moden · June 13th, 2008, 02:50 PM

Heh... no problem... I've made the same mistake a couple of times.

Everyone has to remember... NULL is NOT Nothing! :)

--Jeff Moden