Dashboard > iBATIS DataMapper > 2006 > August > 17 > Problems with WHERE clause involving CHAR data types
Problems with WHERE clause involving CHAR data types
Added by Yusuf Sutarko, last edited by Yusuf Sutarko on Aug 17, 2006
Labels: 
(None)

In some databases like ORACLE,
queries executed from iBatis (maybe because of the jdbc driver?) with WHERE clauses involving CHAR columns, will have zero results, for example :

EMPNO ENAME JOB
7369 SMITH CLERK
7499 ALLEN SALESMAN
7521 WARD SALESMAN
7566 JONES MANAGER
7654 MARTIN SALESMAN
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST

created using this script :

CREATE TABLE EMP (
    EMPNO NUMBER(4) NOT NULL,
    ENAME CHAR(10),
    JOB VARCHAR2(9)
    )
    

and we tried to query :

SELECT * FROM EMP WHERE ENAME='JONES'
    

will results in 0 records, although there should be 1 result.

This is because unlike VARCHAR, CHAR(10) is always treated 10 characters per string, so a workaround for this issue is to query like this

SELECT * FROM EMP WHERE ENAME=rpad('JONES',10,' ')
    

Site running on a free Atlassian Confluence Open Source Project License granted to OSS. Evaluate Confluence today.
Powered by Atlassian Confluence, the Enterprise Wiki. (Version: 2.5.5 Build:#811 Jul 25, 2007) - Bug/feature request - Contact Administrators