Concatenating Results From Sql Query And Null Columns

April 16, 2024 Post a Comment

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, Custo

Solution 1:

wrap coalesce around it

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

Solution 2:

For SQL Server, you have three choices:

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

Solution 3:

For the nullable columns do something like this.

ISNULL(UserState, '')

Solution 4:

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 =@CustomerNumORDERBY UserName

working example:

DECLARE@Userstable (userID int, CustomerNum int,UserName varchar(20), UserAddress varchar(20),  UserCity varchar(20), UserState varchar(20))
INSERT@UsersVALUES (1,111,'Sam','123 First St.', 'city name', 'state name')
INSERT@UsersVALUES (2,111,null,'123 First St.', 'city name', 'state name')
INSERT@UsersVALUES (3,111,'Sam',null, 'city name', 'state name')
INSERT@UsersVALUES (4,111,'Sam','123 First St.', null, 'state name')
INSERT@UsersVALUES (5,111,'Sam','123 First St.', 'city name', null)
INSERT@UsersVALUES (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 ORDERBY userID

OUTPUT:

UserID      CustomerNum UserInfo
----------- ----------- -------------------------------------------1111         Sam -123First St., city name state name
2111123First St., city name state name
3111         Sam - city name state name
4111         Sam -123First St., state name
5111         Sam -123First St., city name 
6111         city name state name

(6row(s) affected)

Solution 5:

You can do this:

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

Lab Sql