Oh I see.
Something fishy is going on.
Eg.
select cast(' ' as varchar) = '' from dual;
-> FALSE
select cast(' ' as char) = '' from dual;
-> TRUE
select coalesce(cast(' ' as varchar), cast(' ' as char)) = '' from dual
-> TRUE

- rami

On 3.11.2014 17:15, Jiunn Jye Ng wrote:
Hi Rami,
Yes. That was my point. Let me try to illustrate in a different way. The result for
select coalesce(cast(' ' as varchar), cast(' ' as char)) from dual; -- both parameter are white space.
should be ' ' (a whitespace). However, h2 is returning '' (empty string).

Rgds,
jay

On Monday, November 3, 2014 10:33:56 PM UTC+8, Rami Ojares wrote:

    coalesce returns the first non-null value in it's arguments.
    Neither '' nor ' ' is null.

    - rami


    On 3.11.2014 12:57, Jiunn Jye Ng wrote:
    Hi All,

    Let's say
       white space = ' '
       empty string = ''
    coalesce should return the first non null value.

    Test Case:
       create table testCoalesce ( colvarchar varchar, colchar char);
       insert into testCoalesce (colvarchar, colchar)  values (' ', ' ');

       select coalesce(colvarchar, colchar),
    length(coalesce(colvarchar, colchar)) from testCoalesce
       return an empty string ''

    I understand that char does not store the trailing spaces. That
    explain where the empty string come from. But the result should
    have been the white space ' '.

    This can be simulated on h2 version 1.4.182.

    Thank you.

    Rgds,
    jay
-- You received this message because you are subscribed to the
    Google Groups "H2 Database" group.
    To unsubscribe from this group and stop receiving emails from it,
    send an email to [email protected] <javascript:>.
    To post to this group, send email to [email protected]
    <javascript:>.
    Visit this group at http://groups.google.com/group/h2-database
    <http://groups.google.com/group/h2-database>.
    For more options, visit https://groups.google.com/d/optout
    <https://groups.google.com/d/optout>.

--
You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]>. To post to this group, send email to [email protected] <mailto:[email protected]>.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "H2 
Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email 
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.

Reply via email to