CASE statement with IS NULL and NOT NULL
Is there any better way to write the lines below in SQL Server 2005?
CASE WHEN (ID IS NULL) THEN 'YES' WHEN (ID IS NOT NULL) THEN 'NO' END AS ID_Value,I'm not familiar with MSSQL but if it has something like IF-function in MySQL, you can write such code: `IF(ID IS NULL, 'YES', 'NO') AS ID_Value`
SQL Server 2012 has `IIF` but the question is tagged 2005.
You should be able to accomplish this with the statement below. `isnull(nullif(isnull(ID,'Y'),ID),'N')`
@Jason Assuming `ID` is a string, of course. Otherwise this expression should be elongated a little more by a call to `CAST` or `CONVERT`.
Tried using `COALESCE`? `CASE COALESCE(ID,0) WHEN 0 THEN 'YES' ELSE 'NO' END`
@Bappy1988 Why would it be better than Jay's answer?
This does not work if the column in the test is of IMAGE datatype. SQL Server throws the following error: "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator."
You are looking for something like the ?: operator in C# and there is no direct equivalent in tsql https://msdn.microsoft.com/en-us/library/ty67wk28.aspx I suspect you could write a function to do it https://msdn.microsoft.com/en-us/library/ms186755.aspx
Did you try:
CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value,I only have access to 2008 right now, but I'd hope that this syntax would still work in 2005 (seems like something that would be part of the original definition of
CASE).Yes - I did try `CASE WHEN (ID IS NULL) THEN 'YES' ELSE 'NO' END AS ID_Value` But I am looking for some other better approach something like `IF(ID IS NULL, 'YES', 'NO') AS ID_Value` in the Ms Sql, so that everything can be in a single line. Any suggestions please
This answer is in a single line. If you want you can put the whole statement in a single line.
License under CC-BY-SA with attribution
Content dated before 6/26/2020 9:53 AM
Wesley 8 years ago
Define "better".