On Thu, Jul 23, 2009 at 3:39 PM, Matt Neimeyer<matt@xxxxxxxxxxxx> wrote:
>> You might even be able to convert EMPTY(X) to COALESCE(X, '') = ''.
>> MySQL seems to be pretty forgiving with its implicit type-casting.
>
> Hmm... The new system I've written properly handles the datatype and
> EMPTY... So this would be a hack to much around with regexs to replace
> EMPTY in customer built selects.
>
> Now I fiddled around with this on 5.1.33-community on Windows and I
> get the following odd results...
>
> select coalesce(0,"")=0,coalesce("","")=0,coalesce(0,"")=0,coalesce("","")=0;
> returns 1, 1, 1, 1
>
> but...
>
> select coalesce(0,"")="",coalesce("","")="",coalesce(0,"")="",coalesce("","")="";
> returns 0, 1, 0, 1
>
> Which implies that in certain circumstances "" = 0 but 0 != "" (unless
> I'm missing something).
>
> Either way it looks like I can use coalesce(X,"")=0 which should be useful!
>
> Matt
>
That is interesting. The whole purpose of the COALESCE function in SQL
is to substitute a non-null value for a null value. Since there aren't
any nulls in either of your statements, you are effectively running
these:
select 0=0, ''=0, 0=0, ''=0
select 0='', ''='', 0='', ''=''
COALESCE(X, '') = 0 is probably the better option though, since the
result of COALESE should be implicitly cast to the datatype of X
before performing the equality comparison. In MySQL, casting an empty
string to an integer would result in the value 0 while casting to a
date would produce the date value equivalent to the integer 0.
The benefit of COALESCE is that it should be standard SQL. I know it
works on MySQL and SQL Server, and I think it works on Oracle and
others as well.
You may take a performance hit for using COALESCE in conditions like
that since it usually means the condition can't use an index, but
considering you're moving from using similar logic in FoxPro that may
not matter for you.
Andrew
--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
