QUICKIE: Null versus Blank?

[webmaster1]

Hi All,

 

I'm using a basic select query puts in a default value if null is returned:

 

SELECT masterdataid, 
IFNULL(resultsnotations.prospectrating, 'Not Rated') AS prospectratingch, 
IFNULL(resultsnotations.lastactivity, 'Loaded') AS lastactivity
FROM resultsnotations
WHERE masterdataid = '339' and prospectrating is null
ORDER BY resultsnotations.lastactivity DESC 

 

For some reason when I output the row the default value doesn't show.

Furthermore when I query for rows will null I can't find any.

 

Am I confusing terminologies here? Is there a difference between NULL and BLANK?

 

[webmaster1]

I think what I'm asking is what's the difference between '' and NULL and how should work this into the IFNULL.

[soak]

and (prospectrating is null OR prospectrating = '')

 

That what you mean?

[webmaster1]

and (prospectrating is null OR prospectrating = '')

 

That what you mean?

 

That's exactly it. Any idea's how to work this into the IFNULL condition I'm using?

[soak]

exactly as typed:

SELECT masterdataid,
IFNULL(resultsnotations.prospectrating, 'Not Rated') AS prospectratingch,
IFNULL(resultsnotations.lastactivity, 'Loaded') AS lastactivity
FROM resultsnotations
WHERE masterdataid = '339' and (prospectrating is null OR prospectrating = '')
ORDER BY resultsnotations.lastactivity DESC 

[webmaster1]

I want to apply it here:

 

IFNULL(resultsnotations.prospectrating, 'Not Rated') AS prospectratingch

 

and not at the WHERE part.

Example: If prospectrating is null or blank then its default value is 'not rated'.

[nankoweap]

null and the empty string are two totally different things. null is essentially undefined, not set, nothing, etc.  the empty string is something - it's a string with a length of zero.

[webmaster1]

null and the empty string are two totally different things. null is essentially undefined, not set, nothing, etc.  the empty string is something - it's a string with a length of zero.

 

Thanks for the run-through. Any ideas on how I check for string length using the IFNULL as well?

[nankoweap]

look into using case rather than ifnull in your query...

 

http://dev.mysql.com/doc/refman/6.0/en/control-flow-functions.html

 

that should do the trick.

 

jason

[soak]

Beat me to it nankoweap

 

Ahhh, apologies, don't know how I missed those.

 

You could use a case.

 

[webmaster1]

I've read through both links though I still don't understand. Could someone dumb this down for me a little more?

 

[webmaster1]

Yep, still not making any sense of this.

 

All I need is to check if string blank as well as if null.

[soak]

SELECT CASE `field`

WHEN ''

THEN 'Value'

ELSE IFNULL( `field` , 'Value' )

END FROM `table`