"venkat akella" <[EMAIL PROTECTED]> wrote: > Hi > > 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 . > > Can some one please comeabck on this. >
length(NULL) is NULL, and <anything>+NULL is NULL. So if any of hour four columns contains a NULL, the result is NULL. Strange though this may seem, it is what SQL is suppose to do. You can work around the problem by doing: length(coalesce(col1,'')) + length(coalesce(col2,'')) +... -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
