Concatenating Fields With Possible Null Values

  • Thread starter Thread starter Robert Gruenloh
  • Start date Start date
R

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
 
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
 
The SQL equivalent of the Nz function is the isnull function:
Lastname + ', ' + Firstname + ' ' + isnull(Middleinitial,'')
(unlike nz, the 2nd argument is required)
 
Dan Goldman said:
The SQL equivalent of the Nz function is the isnull function:
Lastname + ', ' + Firstname + ' ' + isnull(Middleinitial,'')
(unlike nz, the 2nd argument is required)
Click to expand...

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
 
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)




.
Click to expand...
 
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
 
Back
Top