Have Geek Brain

Concatenating results from SQL queries and NULL columns

I need to concatenate multiple columns of a table into one value and then show that value in the asp dropdown. The SQL I am issuing looks like this:

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

Then I set "UserInfo" as the textbox in the dropdown.

This usually works except when one of the columns in the database is null (like UserState). When this happens, the entire concatenation is null and I get an empty entry in the dropdown.

Is there something in SQLServer that will allow me to ignore these NULL results, or do I need to code something in the DataBind event?

thanks

+2


a source to share


6 answers


For nullable columns, you can do something like this.



ISNULL(UserState, '')
+1


a source


wrapping around it



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


a source


For SQL Server, you have three options:

  • IsNull . This is the oldest and most compatible method, although it does not exist in SQL Server Compact Edition (not sure if it is). It takes two arguments and returns the first of the two values, which is not null, or null if both.
  • Coalesce . It is newer and preferred for new design. Similar IsNull

    , but can take more than two arguments. Likewise IsNull

    , it will return the first non-empty argument, or null if that's all.
  • CONCAT_NULL_YIELDS_NULL . This is a database parameter that can be set to ON

    or OFF

    . The meaning should be self-explanatory, but here's the MSDN link .
+2


a source


Use NULL concatenation to your advantage, it 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)
+1


a source


You can do it:

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


a source


If you want to ignore null results (exclude them), you can add what I have below to yours WHERE

. ISNULL

or COALESCE

can be used to select blank rows for 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
0


a source






More articles:


All Articles