Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


T-SQL How to Get Rid of NULL Through COALESCE But within CAST

Posted on 2014-01-24
Medium Priority
Last Modified: 2014-01-25

I need to put COALESCE(... , '') in the CAST clause below, so that NULLs are not returned.  

What's the syntax to do so?

CASE WHEN p.RowNumber = 1 THEN CAST(p.[SUMQTY] + n.[SUMQTY] AS VARCHAR(20)) ELSE '' END AS [Quantity Remaining]


Question by:TBSupport
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

ravikantninave earned 2000 total points
ID: 39807682
Try like this:
CASE WHEN p.RowNumber = 1 THEN CAST(COALESCE(p.[SUMQTY],0) + COALESCE(n.[SUMQTY],0) AS VARCHAR(20)) ELSE '' END AS [Quantity Remaining]
LVL 66

Expert Comment

by:Jim Horn
ID: 39807684
If we're talking NULL's in SUMQTY, and assuming they are numeric, then...

CASE WHEN p.RowNumber = 1
   ELSE '' END AS [Quantity Remaining]

If we're talking only two values then ISNULL will work.
COALESCE does it too, but can also handle more than two values.
LVL 10

Expert Comment

ID: 39807709
Coalesce is ANSI standard, so it has my vote =)

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question