CODEWITHSUNDEEP
sqlsql-servert-sql
SBB
SBB

Reputation: 8970

TSQL get data regardless of Null value

I have a stored procedure that is getting information from my employee table and returning the data.

There are 3 columns that are used:

 B.[SiloDesc] + ' (' + B.[TitleDesc] + ') ' + B.[SkillSetDesc] as SkillSetDesc,

My issue is, if one of those happens to be null, it wont display any of the data. What is the best way to have it include the data regardless of if one of those fields are null.

Upvotes: 2

Views: 86

Answers (2)

canon
canon

Reputation: 41675

You could use coalesce() or isnull() for each individual column... or you could simply use...

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string.

A.: Remove the parentheses when TitleDesc is null:

select concat(B.[SiloDesc], ' (' + B.[TitleDesc] + ')', ' ' + B.[SkillSetDesc])

Because of the way null is treated in sql, the expression ' (' + null + ')' results in null which concat() will treat as an empty string... which is kind of nice as it effectively removes the parentheses if the value is null.

B.: Keep the parentheses regardless:

select concat(B.[SiloDesc], ' (', B.[TitleDesc], ') ', B.[SkillSetDesc])

Samples:

select concat('john', ' (' + null + ')', ' adams') -- john adams
select concat('john', ' (test)', ' ' + null) -- john (test)
select concat('john', ' (the man)', ' adams') -- john (the man) adams

Upvotes: 2

Consult Yarla
Consult Yarla

Reputation: 1150

  isnull(B.[SiloDesc], '') 
  + ' (' + isnull(B.[TitleDesc], '') + ') ' 
  + isnull(B.[SkillSetDesc], '') as SkillSetDesc,

Upvotes: 2

Related Questions