0

I have a c_name column and a name column, would like get c_name if it isn't null or empty string.

I have tested the four SQL queries below. (SQL Server 2012)

  • All queries will return name when c_name is a empty string.

  • Only last query will return name when c_name is null.

If null is not equal to anything, why last statement will return correctly?

select case c_name when '' then name else c_name end as name from employee
select case ltrim(rtrim(c_name)) when '' then name else c_name end as name from employee
select case when ltrim(rtrim(c_name)) = '' then name else c_name end as name from employee
select case when ltrim(rtrim(c_name)) != '' then c_name else name end as name from employee

Update

I test the first comment method, isnull(nullif(c_name, ''), name), it can't really return a complete name as expected. ISNULL truncate your string as checking expression data type. Use COALESCE instead.

Something about COALESCE and CASE: sql - Coalesce vs Case - Stack Overflow

select coalesce(nullif(c_name, ''), name) from employee

More interesting facts about empty string, seems I don't need to care about the how much the space in the string, so the ltrim(rtrim()) seems can be removed. See SQL Server 2008 Empty String vs. Space - Stack Overflow.

Some tests result below, or you can test by yourself in SQL Fiddle

Suppose we use a table like this to test:

c_name name
NULL name with c_name null
name with c_name empty 
select case c_name when '' then name else c_name end as name from employee
select case c_name when '' then name else c_name end as name from employee
select case when c_name = '' then name else c_name end as name from employee

These three statements will return the same result as:

name
NULL
name with c_name empty 
select case when c_name != '' then c_name else name end as name from employee
select coalesce(nullif(c_name, ''), name) from employee

Both these statements can return the expected result as:

name
name with c_name null
name with c_name empty 
select isnull(nullif(c_name, ''), name) as name from employee

This statement returns a truncated string.

name
name
name
Jun Yu
  • 375
  • 1
  • 5
  • 21
  • 3
    `isnull(nullif(c_name, ''), name)` – lptr Feb 15 '22 at 02:43
  • @DaleK but the last query, when `null` is not equal to empty string, I return `c_name` – Jun Yu Feb 15 '22 at 03:00
  • @lptr this query result seems only return part of name string – Jun Yu Feb 15 '22 at 03:01
  • @DaleK Yeah, I mean my logic return `c_name` for last query when not equal to empty string, but it will correctly return `name` – Jun Yu Feb 15 '22 at 03:02
  • 2
    I see your confusion, null is not "equal" or "not equal' to a blank string. Thats why one should be very careful using a not equal or not in comparison when the values can include null - because its not necessarily the result one might expect. – Dale K Feb 15 '22 at 03:10
  • 2
    Remember: **null does not mean "empty"**. Instead, it means "I don't know". So when you compare "I don't know" to an empty string, the result is still "I don't know"... it might be empty, it might not, and you can't tell until the value is populated. This is **also true for the opposite "not equals" check**: the answer is still "you don't know", which when forced to be treated as a conditional result is falsy. Lastly, for completeness, if you compare "I don't know" to another "I don't know", the answer is _still_ "I don't know". – Joel Coehoorn Feb 15 '22 at 05:25

0 Answers0