2010-02-08

Equality -comparing text

Should we use case clause or still use decode? Yet again someting to be aware. Seems like case clause is trimming before comparing. And the same with DB2 minus. Postgres does not trim.

Oracle


SQL> select case when 'a' = 'a ' then 'same' else 'different' end as test from dual;

TEST
---------
same

SQL> select 'a' from dual minus select 'a ' from dual;

'A
--
a

SQL> select decode('a','a ','same','different') testdecode from dual;

TESTDECOD
---------
different




DB2



db2 => select case when 'a' = 'a ' then 'same' else 'different' end as test from sysibm.sysdummy1

TEST
---------
same

1 record(s) selected.

db2 => select 'a' from sysibm.sysdummy1 minus select 'a ' from sysibm.sysdummy1

1
--

0 record(s) selected.



Postgres



postgres=# select case when 'a' = 'a ' then 'same' else 'different' end as test ;
test
-----------
different
(1 row)

postgres=# select 'a' as a except select 'a ' as a;
a
---
a
(1 row)



SQL Server



1> select case when 'a' = 'a ' then 'same' else 'different' end as test ;
2> go
test
----
same

(1 rows affected)
1>
2> select 'a'
3> except
4> select 'a '
5> go

--

(0 rows affected)

5 comments:

  1. Hi,

    I think is not an issue with case. Using if then else happens the same:

    begin
    if 'a' = 'a ' then dbms_output.put_line('same');
    else dbms_output.put_line('different');
    end if;
    end;
    /
    same

    It's related to literals being char o varchar.

    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#sthref869

    SQL>

    DECLARE
    2 last_name1 CHAR(5) := 'BELLO';
    3 last_name2 CHAR(10) := 'BELLO '; -- note trailing blanks
    4 BEGIN
    5 IF last_name1 = last_name2 THEN
    6 DBMS_OUTPUT.PUT_LINE ( last_name1 || ' is equal to ' || last_name2 );
    7 ELSE
    8 DBMS_OUTPUT.PUT_LINE ( last_name2 || ' is not equal to ' || last_name1 );
    9 END IF;
    10 END;
    11 /
    BELLO is equal to BELLO

    Procedimiento PL/SQL terminado correctamente.

    SQL>

    DECLARE
    2 last_name1 VARCHAR2(10) := 'DOW';
    3 last_name2 VARCHAR2(10) := 'DOW '; -- note trailing blanks
    4 BEGIN
    5 IF last_name1 = last_name2 THEN
    6 DBMS_OUTPUT.PUT_LINE ( last_name1 || ' is equal to ' || last_name2 );
    7 ELSE
    8 DBMS_OUTPUT.PUT_LINE ( last_name2 || ' is not equal to ' || last_name1 );
    9 END IF;
    10 END;
    11 /
    DOW is not equal to DOW

    Procedimiento PL/SQL terminado correctamente.
    SQL>

    select * from dual where 'a' = 'a ';
    D
    -
    X

    1 fila seleccionada.

    SQL>

    select * from dual where cast('a' as varchar2(2)) = cast('a ' as varchar2(2));

    ninguna fila seleccionada

    ReplyDelete
  2. It seems than decode treat literals a varchar2:

    select decode('a','a ','same','different') testdecode from dual;
    TESTDECOD
    ---------
    different

    1 fila seleccionada.

    SQL>

    select decode(cast('a' as char(2)),cast('a ' as char(2)),'same','different') testdecode from dual;
    TESTDECOD
    ---------
    same

    1 fila seleccionada.

    ReplyDelete
  3. Generally a literal string is treated as a CHAR in Oracle SQL. You can see that by creating a view as SELECT 'STRING' a FROM DUAL, then describing the view. With that logic, the CASE statement correctly states that 'a' is 'a ' as ignoring trailing space is the comparison characteristics of CHAR values of different lengths.

    In the MINUS select, the resultant datatype is a VARCHAR2.

    If you look at DECODE in DBA_ARGUMENTS, it caters for the first parameter being VARCHAR2 (and DATE, NUMBER) but not CHAR. So the CHAR literal is implicitly converted to a VARCHAR2.

    ReplyDelete
  4. Thank you for the comments. Using views as in Data types in a view

    create view st0001 as SELECT 'S' a FROM DUAL;

    create view st0006 as SELECT 'STRING' a FROM DUAL;

    create view st0025 as SELECT 'STRING7890123456789012345' a FROM DUAL;

    create view st0101 as
    SELECT 'STRING78901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901' a
    FROM DUAL;

    create view st0405 as
    SELECT 'STRING789012345678901234567890...addto405chars a
    FROM DUAL;

    create view st1621 as
    SELECT 'STRING78901234567890...addto1621chars a
    FROM DUAL;

    select table_name,data_type,data_length
    from user_tab_cols
    where table_name like 'ST%'
    order by table_name
    ;

    TABLE_NAM DATA_TYPE DATA_LENGTH
    --------- --------- -----------
    ST0001 CHAR 4
    ST0006 CHAR 24
    ST0025 CHAR 100
    ST0101 CHAR 404
    ST0405 CHAR 1620
    ST1621 CHAR 4000

    6 rows selected.

    Seems like similar lenths that cause rows to V$SQL_SHARED_CURSOR. But that is another story.

    ReplyDelete
  5. asktom explains the case. Casting and Char datatype, as SQL standard says.

    ReplyDelete

About Me

My photo
I am Timo Raitalaakso. I have been working since 2001 at Solita Oy as a Senior Database Specialist. My main focus is on projects involving Oracle database. Oracle ACE alumni 2012-2018. In this Rafu on db blog I write some interesting issues that evolves from my interaction with databases. Mainly Oracle.