venkat akella wrote:
lenght() function in SQL Query is not behaving properly. I have
explained this below with an example.
Select length(col1) + length(col2) + length(col3) + length(col4) from
TestTable;
For example, there are four columns in a table and in a row three
columns (col1, col2 and col3) have propervalues inserted. col4 has NULL.
When I execute the above query on that table, then it doesnt return any
thing. Basically if I just do "select length(col1) + length(col2) +
length(col3) from TestTable" it properly returns the sum of the
lengths of
three columns. But when I include length(col4) in the sum which has NULL,
then it doesnt return anything. I was expecting it to return the sum of
lengths of col1, col2 and col3 even after including the lenght(col4)
in the
sum.
That means, even if one column is NULL, then its effecting the whole
query.
Above explained behaviour is same irrespective of using command line or C
API. I am using sqlite-3.3.8 .
The length function is behaving as it should, your expectations are
wrong :-)
The length of a NULL value is unknown or NULL. The sum of anything and a
NULL value is also NULL or unknown.
You need to subsitute a known string for the NULL values from the table
so you can get the desired result.
select length(coalesce(col1, '')) + length(coalesce(col2, '') + ....
The coalesce function returns the first non NULL value in its arguments.
If the column is NULL it wil return the empty string (an known string of
length zero).
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
