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)
Hi,
ReplyDeleteI 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
It seems than decode treat literals a varchar2:
ReplyDeleteselect 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.
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.
ReplyDeleteIn 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.
Thank you for the comments. Using views as in Data types in a view
ReplyDeletecreate 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.
asktom explains the case. Casting and Char datatype, as SQL standard says.
ReplyDelete