T-SQL CASE Clause How to specify WHEN NULL

Question

I wrote a T-SQL Statement similar like this  the original one looks different but I want to give an easy example here    SELECT first name        CASE last name WHEN null THEN  Max  ELSE  Peter  END AS Name FROM dbo person   This Statement does not have any syntax errors but the case-clause always chooses the ELSE-part - also if the last name is null  But Why   What I want to do is to unite first name and last name  but if last name is null the whole name becomes null   SELECT first name      CASE last name WHEN null THEN    ELSE       last name END AS Name  FROM dbo person   Do you know where the problem is

User · Answer

try   SELECT first name   ISNULL     last name      AS Name FROM dbo person   This adds the space to the last name  if it is null  the entire space last name goes to NULL and you only get a first name  otherwise you get a firts space last name   this will work as long as the default setting for concatenation with null strings is set   SET CONCAT NULL YIELDS NULL ON    this shouldn t be a concern since the OFF mode is going away in future versions of SQl Server

User · Answer

I tried casting to a string and testing for a zero-length string and it worked   CASE     WHEN LEN CAST field value AS VARCHAR MAX      0 THEN         DO THIS END AS field

User · Answer

The issue is that NULL is not considered to be equal to anything even not to itself  but the strange part is that is also not not equal to itself   Consider the following statements  which is BTW illegal in SQL Server T-SQL but is valid in My-SQL  however this is what ANSI defines for null  and can be verified even in SQL Server by using case statements etc     SELECT NULL   NULL -- Results in NULL  SELECT NULL  lt  gt  NULL -- Results in NULL  So there is no true false answer to the question  instead the answer is also null   This has many implications  for example in   CASE statements  in which any null value will always use the ELSE clause unless you use explicitly the WHEN IS NULL condition  NOT the WHEN NULL condition   String concatenation  as SELECT a   NULL -- Results in NULL In a WHERE IN or WHERE NOT IN clause  as if you want correct results make sure in the correlated sub-query to filter out any null values    One can override this behavior in SQL Server by specifying SET ANSI NULLS OFF  however this is NOT recommended and should not be done as it can cause many issues  simply because deviation of the standard    As a side note  in My-SQL there is an option to use a special operator  lt   gt  for null comparison    In comparison  in general programming languages null is treated is a regular value and is equal to itself  however the is the NAN value which is also not equal to itself  but at least it returns  false  when comparing it to itself   and when checking for not equals different programming languages have different implementations    Note however that in the Basic languages  i e  VB etc   there is no  null  keyword and instead one uses the  Nothing  keyword  which cannot be used in direct comparison and instead one needs to use  IS  as in SQL  however it is in fact equal to itself  when using indirect comparisons

User · Answer

You can use IsNull function  select      isnull rtrim ltrim  FirstName                  isnull rtrim ltrim  SecondName                  isnull rtrim ltrim  Surname                  isnull rtrim ltrim  SecondSurname        from TableDat   if one column is null you would get an empty char  Compatible with Microsoft SQL Server 2008

User · Answer

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

User · Answer

CASE WHEN last name IS NULL THEN    ELSE     last name END

User · Answer

When you get frustrated trying this    CASE WHEN last name IS NULL THEN    ELSE     last name END   Try this one instead   CASE LEN ISNULL last Name      WHEN 0 THEN     ELSE       last name END AS newlastName   LEN ISNULL last Name      measures the number of characters in that column  which will be zero whether it s empty  or NULL  therefore WHEN 0 THEN will evaluate to true and return the    as expected   I hope this is a helpful alternative   I have included this test case for sql server 2008 and above   DECLARE  last Name varchar 50    NULL  SELECT  CASE LEN ISNULL  last Name      WHEN 0 THEN     ELSE  A      last name END AS newlastName  SET  last Name    LastName   SELECT  CASE LEN ISNULL  last Name      WHEN 0 THEN     ELSE  A      last name END AS newlastName

User · Answer

Given your query you can also do this   SELECT first name         ISNULL last name      AS Name FROM dbo person

User · Answer

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

User · Answer

CASE       WHEN last name IS null THEN         ELSE       last name  END

User · Answer

Jason caught an error  so this works      Can anyone confirm the other platform versions  SQL Server   SELECT CASE LEN ISNULL last name       WHEN 0 THEN     ELSE       last name END AS newlastName   MySQL   SELECT CASE LENGTH IFNULL last name       WHEN 0 THEN     ELSE       last name END AS newlastName   Oracle   SELECT CASE LENGTH NVL last name       WHEN 0 THEN     ELSE       last name END AS newlastName

User · Answer

Found a solution to this  Just ISNULL the CASE statement  ISNULL CASE x WHEN x THEN x ELSE x END      AS  BLAH

User · Answer

NULL does not equal anything   The case statement is basically saying when the value   NULL    it will never hit  There are also several system stored procedures that are written incorrectly with your syntax   See sp addpullsubscription agent and sp who2  Wish I knew how to notify Microsoft of those mistakes as I m not able to change the system stored procs

User · Answer

Use the CONCAT function available in SQL Server 2012 onward   SELECT CONCAT  FirstName            LastName   FROM YOURTABLE

User · Answer

The problem is that null is not considered equal to itself  hence the clause never matches   You need to check for null explicitly   SELECT CASE WHEN last name is NULL THEN first name ELSE first name         last name

[tsql] T-SQL CASE Clause: How to specify WHEN NULL

Examples related to tsql