Skip to main content

MS SQL Server / SELECT clause with a CASE expression

In SQL Server, if you have a column which has NULLs and instead of nulls, if you want to display 'Nothing', what would you do?


The following query

SELECT CASE Dept_Name WHEN NULL THEN 'Nothing' ELSE Dept_Name END Dept_Name
FROM Inventory

would still display the nulls and not 'Nothing'.

Workaround:






1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W

(9 rows affected)
1>
2> -- Select with a CASE expression:
3>
4> SELECT ID
5> , Name
6> , CASE ID
7> WHEN 1 THEN 'Mountain'
8> WHEN 2 THEN 'Road'
9> WHEN 3 THEN 'Touring'
10> WHEN Null THEN 'Something'
11> ELSE 'No'
12> END As IDX
13> FROM Employee
14> GO
ID Name IDX
----------- ---------- ---------
1 Jason Mountain
2 Robert Road
3 Celia Touring
4 Linda No
5 David No
6 James No
7 Alison No
8 Chris No
9 Mary No

Better than CASE: There is a specific function for this,
ISNULL ( check_expression , replacement_value )

so your syntax would become

SELECT ISNULL( Dept_Name, 'Nothing' ) Dept_Name
FROM Inventory




Sources:
http://www.java2s.com/Code/SQLServer/Select-Query/CasewhenNULL.htm

Comments

Popular posts from this blog

What is .csp extension? C++ Server Pages

C++ Server Pages C++ Server Pages (CSP) is a Web Engine for advanced Web Application Development, that uses blended Markup Language / C++ scripts ( such as HTML/C++, XML/C++, WML/C++ etc.) Similar to ASP and JSP, it provides a great easiness in creating web pages with dynamic content, as well as complex business applications. However, instead of Java, Javascript or VBscript, it uses C++ . This brings some significant advantages: Incredibly high processing efficiency. Benchmarks have shown a range of 80 to 250 times higher processing speed than ASP. The use of pure C++ allows the use of tons of libraries that are currently available. It is important to notice that the libraries written in C++ are tens or hundreds of times more than in any other language. It is widely accepted that the most skilled programmers in the IT market are the C++ ones. However, CGI, ISAPI and other frameworks where C++ applies, do not provide the web developer with facilities for efficient app...

Integrating SwiftUI into Existing AppKit Cocoa Projects: A Step-by-Step Guide

As Apple continues to push SwiftUI as the future of app development across its platforms, many developers find themselves in a position where they need to integrate this modern framework into their existing AppKit Cocoa projects. This integration can breathe new life into older applications, allowing developers to take advantage of SwiftUI's declarative syntax and powerful features while maintaining their existing codebase. In this guide, we'll walk through the process of adding SwiftUI to an existing AppKit Cocoa project, providing you with the knowledge to modernize your macOS applications.