Re: [sqlite] concatenation of row with NULLS suppresses all values

[Jim Dodgen]

thanks Derrell ,
I guess I expect to see just the null values suppressed and the values that 
exist to show up

something like this:
1--
2--
3-33-333
I did not expect the concatenation to alter the behavior of the data that 
was returned. But if that is the way it works then the COALESCE will work 
just fine for me

thanks
Jim
At 08:55 PM 3/28/2005, you wrote:
Jim Dodgen <[EMAIL PROTECTED]> writes:
> drop table foo;
> create table foo (x,y,z);
> insert into foo values (1, null, null);
> insert into foo values (2, null, null);
> insert into foo values (3, 33, 333);
> select x ||  "-" || y ||  "-" || z from foo;
>
> whe I run the above select I only see the last row.
>
> it looks as if a row has any nulls in the concatenation then all the values
> are suppressed.
>
>
> is this the correct behavior?
NULL means there's no value there.  What does it mean to concatenate no value?
Your query actually returns three records, but the first two records have NULL
as the result.  See below.
You can replace your query with something like this if you like:
SELECT
       COALESCE(x, '<null>') || '-' ||
       COALESCE(y, '<null>') || '-' ||
       COALESCE(z, '<null>')
  FROM foo;
sqlite> CREATE TABLE foo (x,y,z);
sqlite> INSERT INTO foo VALUES (1, NULL, NULL);
sqlite> INSERT INTO foo VALUES (2, NULL, NULL);
sqlite> INSERT INTO foo VALUES (3, 33, 333);
sqlite> SELECT ROWID, x || '=' || y || '-' || z AS concatenated FROM foo;
       ROWID = 1
concatenated =
       ROWID = 2
concatenated =
       ROWID = 3
concatenated = 3=33-333
sqlite> SELECT
   ...>     ROWID,
   ...>     COALESCE(x, '<null>') || '-' ||
   ...>     COALESCE(y, '<null>') || '-' ||
   ...>     COALESCE(z, '<null>') AS concatenated
   ...> FROM foo;
       ROWID = 1
concatenated = 1-<null>-<null>
       ROWID = 2
concatenated = 2-<null>-<null>
       ROWID = 3
concatenated = 3-33-333
sqlite>