lve wrote:
> If you are using Oracle 9, you can use:
> select count(*) from mytable where nullif(myfield,'') is not null
>
> Nullif will convert the empty strings to null and thus the statement
> will cover both Null and empty string.
>
> lv
>
> "Chris" <cs123._no_spam__at_telstra.com> wrote in message news:<gbmwb.25063$aT.23972_at_news-server.bigpond.net.au>...
>
>>"Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message
>>news:21k3svgttf4ujetb51q1g3ugds9q6jj45t_at_4ax.com...
>>
>>>On Fri, 21 Nov 2003 15:17:22 +0100, "Tom Miskiewicz"
>>><miskiewicz2_at_yahoo.com> wrote:
>>>
>>>
>>>>Hello!
>>>>
>>>>I want to count those records in a table, where the field I specified is
>>
>> not
>>
>>>>empty. How to make such a query efficient. Is there a different/more
>>>>efficient way to achieve that, then select count(1) from mytable where
>>>>myfield != NULL? Myfield is a varchar2 field. I'm using Oracle 9.
>>>>
>>>>
>>>>Thanks
>>>>Thomas
>>>>
>>>
>>>!= NULL won't work. You can't apply equality or inequality to null as
>>>NULL doesn't compare to anything. SQL implements three-valued logic:
>>>true, false and NULL. You'll need to use IS NOT NULL or > chr(0)
>>>provided the column is indexed.
>>>
>>>
>>>--
>>>Sybrand Bakker, Senior Oracle DBA
>>
>>If all you want to test for is not null and your column is indexed then you
>>should use
>>select count(*)
>>from table
>>where col is not null;
>>
>>AFAIK this will use the index instead of a FTS
'' still is NULL:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
SQL> select nvl('','Is it empty or is it NULL?') from dual;
NVL('','ISITEMPTYORISITNULL?')
Is it empty or is it NULL?
--
Regards, Frank van Bortel
Received on Tue Nov 25 2003 - 15:46:39 CST
