Re: concatenating columns with null values
If you don't want the standard behavior you can use nvl (in 7.3)
select nvl(c1, "") || nvl(c2, "")
from t1
"Pete Smith" <pete.smith@aethos.co.uk> wrote in message
news:38C531A1.70947442@aethos.co.uk...
Hi all
I'm surprised by the result I get when concatenating two columns and one
of them contains a null value.
In this case the returned expression is also null.
for example
create table t1 (c1 integer, c2 integer)
insert into t1 values (1,1);
insert into t1 (c1) values (2);
insert into t1 (c2) values (3);
select c1 || c2 from t1;
gives
(expression) 11
(expression)
(expression)
Am I right to be surprised or should I have expected this?
I was hoping to see something like:
(expression) 11
(expression) 2
(expression) 3
TIA
Pete
Thanks for the replies, folks.
I'll look at using the nvl function.
thanks
Pete
(no longer surprised)
"Manuel A. Daponte Santiago" wrote:
> If you don't want the standard behavior you can use nvl (in 7.3)
>
> select nvl(c1, "") || nvl(c2, "")
> from t1
>
> "Pete Smith" <pete.smith@aethos.co.uk> wrote in message
> news:38C531A1.70947442@aethos.co.uk...
> Hi all
>
> I'm surprised by the result I get when concatenating two columns and one
> of them contains a null value.
> In this case the returned expression is also null.
>
> for example
>
> create table t1 (c1 integer, c2 integer)
> insert into t1 values (1,1);
> insert into t1 (c1) values (2);
> insert into t1 (c2) values (3);>
> select c1 || c2 from t1;
>
> gives
>
> (expression) 11
>
> (expression)
>
> (expression)
>
> Am I right to be surprised or should I have expected this?
>
> I was hoping to see something like:
> (expression) 11
>
> (expression) 2
>
> (expression) 3
>
> TIA
> Pete