Concatenating Fields With Possible Null Values

[Robert Gruenloh]

I'm having trouble in SQL Server with an ADP in
concatenating fields where a value may be null. For
example, creating a single name field from separate last
name, first name, middle initial fields, where the middle
initial may be null. In Access, I just used the "Nz"
function: Lastname + ", " + Firstname + " " + Nz
(Middleinitial). I can't find a way to do this in SQL
Server - the closest I can come is to reset a database
option on how concatenation of null fields is handled,
which appears to have implications for other activities.
Does anyone have any suggestions on how to do this?

Thanks, Robert Gruenloh

 

[Kevin3NF]

You are on the right track...

In your stored procedure (I assume you are using SPs):

Set Concat_Null_Yields_Null Off

Your code goes here

Set Concat_Null_Yields_Null On


--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm

 

[Dan Goldman]

The SQL equivalent of the Nz function is the isnull function:
Lastname + ', ' + Firstname + ' ' + isnull(Middleinitial,'')
(unlike nz, the 2nd argument is required)

 

[Chris Howarth]

The SQL equivalent of the Nz function is the isnull function:
Lastname + ', ' + Firstname + ' ' + isnull(Middleinitial,'')
(unlike nz, the 2nd argument is required)

To cater for all possiblities I would be inclined to us:

isnull(Lastname + ', ','') + isnull(Firstname + ' ','') +
isnull(Middleinitial,'')

Although if the Lastname and Firstname are ever Null then this would suggest
that something is wrong with the validation applied before inserting the
row.

Chris

 

[Guest]

Thanks, Dan - that did it!

-----Original Message-----
The SQL equivalent of the Nz function is the isnull function:
Lastname + ', ' + Firstname + ' ' + isnull (Middleinitial,'')
(unlike nz, the 2nd argument is required)




.

 

[Guest]

Chris -
Thanks for the input. I'm validating last and first names,
so I should be OK. But your suggestion will come in handy
somewhere else, I'm sure.
Thanks, Robert