Concatenating Results From SQL Query And NULL Columns

February 12, 2023 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 = @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)

Solution 5:

You can do this:

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

Lab Sql