concatenating results from SQL query and NULL columns

I need to concatenate several columns of a table into a single value, then show that value in an asp dropdownlist. The SQL code I'm issuing is as follows:

SELECT UserID, CustomerNum, UserName + ' - ' + UserAddress + ',' + UserCity + ' ' + UserState AS UserInfo FROM 开发者_如何学编程 Users WHERE (CustomerNum = @CustomerNum) ORDER BY UserName

I then set 'UserInfo' as the text field in the dropdownlist.

This generally works, except occasionally one of the columns in the database is null (for example, UserState). When that happens, the entire concatenation is null, and I get an empty entry in the dropdownlist.

Is there something in SQLServer that will allow me to ignore those NULL results, or will I have to code something up in the DataBind event?

Thanks

---

wrap coalesce around it

COALESCE(UserName,'') + ' - ' + 
COALESCE(UserAddress,'') + ',' + 
COALESCE(UserCity,'') + ' ' + 
COALESCE(UserState,'') AS UserInfo

---

For SQL Server, you have three choices:

1. IsNull - This is the oldest and most compatible method, though it doesn't exist in SQL Server Compact Edition (don't know if that's relevant). It takes two arguments and returns the first of the two that is non-null, or null if both are.
2. Coalesce - This is newer and preferred for new development. Similar to IsNull, but can take more than two arguments. Like IsNull, it will return the first non-null argument, or null if all are.
3. CONCAT_NULL_YIELDS_NULL - This is a database option that can be set to ON or OFF. The meaning should be self-explanetory, but here's an MSDN link.

---

For the nullable columns do something like this.

ISNULL(UserState, '')

---

Use the NULL concatenation to your advantage, this will remove unnecessary separator characters:

SELECT 
    UserID, CustomerNum
        ,ISNULL(UserName+' - ','')
             +ISNULL(UserAddress+', ','')
             +ISNULL(UserCity+' ','')
             +ISNULL(UserState,'') AS UserInfo 
    FROM Users 
    WHERE CustomerNum = @CustomerNum 
    ORDER BY UserName

working example:

DECLARE @Users table (userID int, CustomerNum int,UserName varchar(20), UserAddress varchar(20),  UserCity varchar(20), UserState varchar(20))
INSERT @Users VALUES (1,111,'Sam','123 First St.', 'city name', 'state name')
INSERT @Users VALUES (2,111,null,'123 First St.', 'city name', 'state name')
INSERT @Users VALUES (3,111,'Sam',null, 'city name', 'state name')
INSERT @Users VALUES (4,111,'Sam','123 First St.', null, 'state name')
INSERT @Users VALUES (5,111,'Sam','123 First St.', 'city name', null)
INSERT @Users VALUES (6,111,null,null, 'city name', 'state name')

SELECT 
    UserID, CustomerNum
        ,ISNULL(UserName+' - ','')
             +ISNULL(UserAddress+', ','')
             +ISNULL(UserCity+' ','')
             +ISNULL(UserState,'') AS UserInfo 
    FROM @Users 
    --WHERE CustomerNum = @CustomerNum 
    ORDER BY userID

OUTPUT:

UserID      CustomerNum UserInfo
----------- ----------- -------------------------------------------
1           111         Sam - 123 First St., city name state name
2           111         123 First St., city name state name
3           111         Sam - city name state name
4           111         Sam - 123 First St., state name
5           111         Sam - 123 First St., city name 
6           111         city name state name

(6 row(s) affected)

---

You can do this:

SELECT UserID, CustomerNum, UserName + ' - ' + 
   ISNULL(UserAddress + ',','') + ISNULL(UserCity,'') + ' ' + ISNULL(UserState,'')
   AS UserInfo 
FROM Users 
WHERE (CustomerNum = @CustomerNum) ORDER BY UserName

---

If you want to ignore null results (exclude them) you can add what i have below to your WHERE. ISNULL or COALESCE can be used to select empty strings for the null columns as described above if that's what you need to do.

...
WHERE UserName is not null
AND   UserAddress is not null
AND   UserCity is not null
AND   UserState is not null