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
