Wrox Programmer Forums
Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
| Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 12th, 2008, 05:47 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 62
Thanks: 0
Thanked 1 Time in 1 Post
Default Concatenating Columns in SELECT Problem

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 June 12th, 2008, 05:59 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old June 12th, 2008, 06:41 PM
Authorized User
 
Join Date: Sep 2004
Location: , , .
Posts: 62
Thanks: 0
Thanked 1 Time in 1 Post
Default

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 June 12th, 2008, 07:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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?
 
Old June 12th, 2008, 07:23 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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 June 13th, 2008, 01:08 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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!
 
Old June 13th, 2008, 02:50 PM
Friend of Wrox
Points: 1,536, Level: 15
Points: 1,536, Level: 15 Points: 1,536, Level: 15 Points: 1,536, Level: 15
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2006
Location: , MI, USA.
Posts: 475
Thanks: 0
Thanked 9 Times in 9 Posts
Default

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

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

--Jeff Moden




Similar Threads
Thread Thread Starter Forum Replies Last Post
concatenating records rashi Access VBA 9 November 6th, 2008 05:07 PM
SELECT VARIABLE Number of columns drani Oracle 4 September 21st, 2008 01:12 AM
Concatenating like records pvasudevan SQL Server 2000 35 August 14th, 2008 09:28 AM
Select a value across 3 different columns jfergy Classic ASP Databases 0 January 23rd, 2006 02:46 AM
vb script + excel - select multiple columns mohit Excel VBA 1 January 21st, 2005 06:11 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.