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 : [EMAIL PROTECTED] [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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" <[EMAIL PROTECTED]> 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? > > [EMAIL PROTECTED] 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
