SQL Problem for SQL Server 7.0 1

[Guest_imported]

I am trying to address an issue in SQL Server 7.0. I have a table called Contacts with three common data entry fields (LastName, FirstName, Title). I need to concatenate the three fields in SQL Server as one field with an alias called 'Contact'. I have a problem because sometimes the FirstName field is null and as a result the entire concatenation is null. For example the concatenated field below called Contact is only blank when the FirstName field is null -- see the record with LastName = Anderson and Title = Maj or LastName = Biernesser and Title = Maj.

Contact LastName FirstName Title
Account, Nonlocal Account Nonlocal
Adamoyurko, Sharon Adamoyurko Sharon
Adams, Allen Adams Allen
Adkins, Mr. Carrington Adkins Carrington Mr.
Anderson Maj.
Anderson, SSgt Donald Anderson Donald SSgt
Anderson, Kay Anderson Kay
Biernesser Maj.


I used the following SQL query that came up with the result described above:

SELECT ISNULL(LastName + ', ' + Title + ' ' + FirstName,
LastName + ', ' + FirstName) AS Contact, LastName,
FirstName, Title
FROM dbo.Contacts
WHERE (ResourceAdvisor = 1)
ORDER BY LastName, FirstName


My problem is that the ISNULL ( ) function does not really work in this instance because it uses 2 arguments but I really have 3 arguments that are required. In the ISNULL function I'm saying that if because the Title field is null the entire concatenation is null, then replace the concate of all three with just LastName and FirstName. But what I also want to include is when the FirstName is null then concatenate the LastName and Title. This is a conditional issue. So instead of 2 conditions, I need to deal with 3.

Can I use the SQL statements ISNULL function with a IF ELSE statement of a SELECT CASE statement?

Help would be greatly appreciated. Thanks,

Cheryl Dixon
email address: Cheryl.Dixon@bolling.af.mil

 

[tlbroadbent]

You can change the behavior of SQL Server when concatenating NULL. The following statement will change from ANSI standadrd behavior and will return a non NULL result even if one of the columns is NULL.

SET CONCAT_NULL_YIELDS_NULL OFF

Or you can use a combination of CASE and ISNULL functions to get the result you want.

SELECT Contact=
CASE WHEN LastName IS NOT NULL
THEN LastName + ', ' ELSE '' END +
CASE WHEN Title IS NOT NULL
THEN Title + ' ' Else '' End +
CASE WHEN FirstName IS NOT NULL
THEN FirstName ELSE '' END,
LastName, FirstName, Title
FROM ... Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[r937]

COALESCE, while certainly not as flexible as CASE, is exactly what this example calls for...

SELECT Contact=
COALESCE(LastName+', ','')
+ COALESCE(Title+' ','')
+ COALESCE(FirstName,'')

rudy

http://rudy.ca/

 

[tlbroadbent]

Good tip, Rudy! I forget about Coalesce because I use the other functions so frequently. It is nice to be remoneded of alternate and better ways to do things. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Guest_imported]

Thanks Terry and Rudy,

I tried all three and decided to simply use the first answer of changing the behavior of SQL Server using SET CONCAT_NULL_YIELDS_NULL OFF in a stored procedure and it worked great. So thanks again.

CherylD