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>
