List:General Discussion« Previous MessageNext Message »
From:Gelu Gogancea Date:November 28 2005 9:44pm
Subject:RE: How to use Logic in View Statment?
View as plain text  
Hi ,
IMHO the corect way is to check if IS NULL 

SELECT 
fieldOne,
fieldTwo,
IF(fieldThree IS NULL,fieldFour,fieldThree) as Company
FROM table;

Depening on the column definition(if is byte,multi-byte...etc.),  LENGTH() function can
have various behaviour and is not quite sure that you will get always the same result.

Regards

_____________________________________________________
G.NET SOFTWARE COMPANY
SYSTEM INTEGRATOR - AUTOMATION & SOFTWARE DEVELOPER
http://www.gonetsoftware.com

Permanent e-mail address :
gelu.gogancea@stripped
gelu.gogancea@stripped


-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: Monday, 28 November, 2005 10:09 PM
To: Cory @ SkyVantage
Cc: My SQL
Subject: Re: How to use Logic in View Statment?


"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