List:General Discussion« Previous MessageNext Message »
From:SGreen Date:November 28 2005 8:08pm
Subject:Re: How to use Logic in View Statment?
View as plain text  
"Cory @ SkyVantage" <lasso@stripped> wrote on 11/28/2005 02:38:50 
PM:

> I have a similar question.   How do you test if the field in question 
> isn't NULL or ''  (empty string)?   Is the LENGTH a good test or is 
> there a better way?
> 
> SGreen@stripped wrote:
> 
> >LENGTH does exist (as a function):
> >http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
> >
> >and here is how to use the IF() function:
> >http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
> >
> >I believe this is the equivalent statement you wanted:
> >
> >SELECT 
> >  fieldOne,
> >  fieldTwo,
> >  IF(CHAR_LENGTH(fieldThree)>0,fieldThree,fieldFour) as Company
> >FROM table;
> >
> 

There are MANY ways to work with or detect NULL values:

ISNULL()
COALESCE()
IS NULL
IS NOT NULL
NOT .. IS NULL
>=''
<=> NULL
IFNULL()
NULLIF()

http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html

What exactly are you trying to accomplish with the test? There may be 
several ways of getting at what you want.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine

The ISNULL function returns either a 0 or 1 depening on if the expression 
inside the parentheses is NULL or not
Thread
How to use Logic in View Statment?Scott Klarenbach28 Nov
  • Re: How to use Logic in View Statment?SGreen28 Nov
    • Re: How to use Logic in View Statment?Cory @ SkyVantage28 Nov
      • Re: How to use Logic in View Statment?SGreen28 Nov
    • Re: How to use Logic in View Statment?Martijn Tonies28 Nov
    • Re: How to use Logic in View Statment?Scott Klarenbach5 Dec
      • Re: How to use Logic in View Statment?SGreen5 Dec
  • Re: How to use Logic in View Statment?Peter Brawley28 Nov
RE: How to use Logic in View Statment?Gelu Gogancea28 Nov