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

Hello:

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]

Thanks!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?
 
ravikantninaveConnect With a Mentor Commented:
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]
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If we're talking NULL's in SUMQTY, and assuming they are numeric, then...

CASE WHEN p.RowNumber = 1
   THEN CAST(ISNULL(p.[SUMQTY],0) + ISNULL(n.[SUMQTY],0) AS VARCHAR(20))
   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.
0
 
PadawanDBAOperational DBACommented:
Coalesce is ANSI standard, so it has my vote =)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.