Testing database value for NULL versus empty string

[Brad Lindsay-2]

Is there a way to test if a value returned from the database is NULL  
versus just an empty string?  Essentially I need a way to test if a  
column for a row has returned NULL without knowing what data type that  
field is and whether or not that field can have NULL values. I will  
then copy that data into a new row, so it's important to know whether  
or not the value is actually NULL.


Thanks,
-Brad

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Jason Huck]

There's some automatic type casting that happens on field() that might
trip this up, but have you tried this?

field('foo') === null

http://reference.lassosoft.com/Reference.LassoApp?1115

- jason



On Fri, Oct 30, 2009 at 10:29 AM, Brad Lindsay <blindsay@...> wrote:

> Is there a way to test if a value returned from the database is NULL versus
> just an empty string?  Essentially I need a way to test if a column for a
> row has returned NULL without knowing what data type that field is and
> whether or not that field can have NULL values. I will then copy that data
> into a new row, so it's important to know whether or not the value is
> actually NULL.
>
>
> Thanks,
> -Brad
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>
>
>

--
tagSwap.net :: Open Source Lasso Code
<http://tagSwap.net/>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Brad Lindsay-2]

This worked for me. Don't know why I didn't try it, guess I thought  
the casting occurred before I would have a chance to.

Thanks Jason!

-Brad


On Oct 30, 2009, at 10:35 AM, Jason Huck wrote:

> There's some automatic type casting that happens on field() that might
> trip this up, but have you tried this?
>
> field('foo') === null
>
> http://reference.lassosoft.com/Reference.LassoApp?1115
>
> - jason
>
>
>
> On Fri, Oct 30, 2009 at 10:29 AM, Brad Lindsay <blindsay@...>  
> wrote:
>> Is there a way to test if a value returned from the database is  
>> NULL versus
>> just an empty string?  Essentially I need a way to test if a column  
>> for a
>> row has returned NULL without knowing what data type that field is  
>> and
>> whether or not that field can have NULL values. I will then copy  
>> that data
>> into a new row, so it's important to know whether or not the value is
>> actually NULL.
>>
>>
>> Thanks,
>> -Brad
>>
>> --
>> This list is a free service of LassoSoft: http://www.LassoSoft.com/
>> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
>> Manage your subscription: http://www.ListSearch.com/Lasso/
>>
>>
>>
>
>
>
> --
> tagSwap.net :: Open Source Lasso Code
> <http://tagSwap.net/>
>
> --
> This list is a free service of LassoSoft: http://www.LassoSoft.com/
> Search the list archives: http://www.ListSearch.com/Lasso/Browse/
> Manage your subscription: http://www.ListSearch.com/Lasso/
>
>
>

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Israel Thompson]

On a further note, if you are using -SQL statements, you can use the
following to isolate records with empty or null fields:

fieldname IS NULL
fieldname = ""

I've found that fieldname = null doesn't quite work the same as
fieldname IS NULL, so in my personal experience I stick exclusively to
the latter when isolating null values.

Israel

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Steve Piercy]

On 11/2/09 at 5:37 PM, thompson.israel@... (Israel
Thompson) pronounced:

>On a further note, if you are using -SQL statements, you can use the
>following to isolate records with empty or null fields:
>
>fieldname IS NULL
>fieldname = ""

These are two SQL statements are not equivalent.  The first
tests for a NULL value in the field, the second for an empty
string value.

Also to find fields not NULL:

fieldname IS NOT NULL

--steve

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
-- --
Steve Piercy               Web Site Builder              
Soquel, CA
<web@...>                  <http://www.StevePiercy.com/>


--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/

[Johan Solve]

On Tue, Nov 3, 2009 at 12:37 AM, Israel Thompson
<thompson.israel@...> wrote:

> On a further note, if you are using -SQL statements, you can use the
> following to isolate records with empty or null fields:
>
> fieldname IS NULL
> fieldname = ""
>
> I've found that fieldname = null doesn't quite work the same as
> fieldname IS NULL, so in my personal experience I stick exclusively to
> the latter when isolating null values.
>
> Israel

These pages tell more about how to work with NULL values and why you
can't use = to compare with NULL.

http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html
http://dev.mysql.com/doc/refman/5.0/en/problems-with-null.html



--
Mvh
Johan Sölve
____________________________________
Montania System AB
Halmstad   Stockholm   Malmö
http://www.montania.se

Johan Sölve
Mobil [phone]
johan@...

Kristinebergsvägen 17, S-302 41 Halmstad, Sweden
Telefon [phone] |  Fax [phone]

--
This list is a free service of LassoSoft: http://www.LassoSoft.com/
Search the list archives: http://www.ListSearch.com/Lasso/Browse/
Manage your subscription: http://www.ListSearch.com/Lasso/