IFNull in MySQL

Question

I am new to SQL and I am trying to create a calculated field using a set of columns. However, some of those values may be NULL. If they are NULL, I don't want the calculated field to return a NULL result but instead set some arbitrary value.

Here is the calculated field

(ces.EXPERT_SCORE * cirm.CONSUMER_RATING) + (12.5 * scs.SIMILARITY)

You have the function, here's [the documentation](http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull) -- what do you need us for? — OMG Ponies, Jan 27 '11 at 22:50

score 1 · Accepted Answer · answered Jan 27 '11 at 22:51

1

use IFNULL(tocheckwhat,withwhattoreplace) so:

(IFNULL(ces.EXPERT_SCORE,5) * IFNULL(cirm.CONSUMER_RATING,5)) + (12.5 * IFNULL(scs.SIMILARITY,5))

should work! :)

answered Jan 27 '11 at 22:51

FeRtoll

1,247
11
26

score 0 · Answer 2 · answered Jan 27 '11 at 22:51

0

You can use COALESCE operator

(
 COALESCE(ces.EXPERT_SCORE, <YOUR_ARBIT_VALUE>) * 
 COALESCE(cirm.CONSUMER_RATING, <YOUR_ARBIT_VALUE>)
)
+ (12.5 * COALESCE(scs.SIMILARITY, <YOUR_ARBIT_VALUE>))

answered Jan 27 '11 at 22:51

Chandu

81,493
19
133
134

IFNull in MySQL

2 Answers2