Substitute a value for zero in SQL Server

I have a list of products, some of which contain a default weight and some of which do not.
I need to create a list where this weight is used as a multiplier. I can't chenge the data as this would cause problmes elsewhere.

What I am looking for is something like COALESCE(BoxWeight, 25) but which would work for a zero value rather that a Null value as is the case for COALESCE

Any ideas?
grwallaceAsked:
Who is Participating?
 
joshbennerConnect With a Mentor Commented:
Use a CASE expression.

See: http://msdn.microsoft.com/en-us/library/ms181765(SQL.90).aspx

Example:

SELECT
  ProductName,
  Weight,
  WeightMultiplier = CASE BoxWeight WHEN 0 THEN 25 ELSE BoxWeight
FROM
  Products
0
 
bmatumburaConnect With a Mentor Commented:
CASE WHEN BoxWeight = 0 Then 25 ELSE BoxWeight END
0
 
DimitrisConnect With a Mentor Senior Solution ArchitectCommented:
use case
for example :

SELECT CASE WHEN ISNULL(BoxWeight,0 ) =0 THEN 25 * something
                       ELSE BoxWeight * something
             END as Result
FROM My_Table
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.