Menu
The SQL CASE Statement If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.
SQL offers two case abbreviations to cope with null : coalesce and nullif . Both are used like functions and do not use the keywords case , when , then , else and end .
The CASE statement has 2 forms which need to be understood. In this form each boolean_expression is evaluated sequentially and the first to return true is executed. Here you can use the IS operator that is used to test for NULL values. e.g. WHEN column IS NULL THEN.
CASE WHEN last_name IS NULL THEN '' ELSE ' '+last_name END
The WHEN part is compared with ==, but you can't really compare with NULL. Try
CASE WHEN last_name is NULL THEN ... ELSE .. END
instead or COALESCE:
COALESCE(' '+last_name,'')
(' '+last_name is NULL when last_name is NULL, so it should return '' in that case)
There are plenty of solutions but none covers why the original statement doesn't work.
CASE last_name WHEN null THEN '' ELSE ' '+last_name
After the when, there is a check for equality, which should be true or false.
If one or both parts of a comparison is null, the result of the comparison will be UNKNOWN, which is treated like false in a case structure. See: https://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/
To avoid this, Coalesce is the best way.
Given your query you can also do this:
SELECT first_name + ' ' + ISNULL(last_name, '') AS Name FROM dbo.person
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With