Results 1 to 6 of 6

Thread: empty string

  1. #1
    Join Date
    Oct 2003
    Posts
    20

    Unanswered: empty string

    Hi I wanna ask if someone knows how we could compare a column with an empty sting ('').
    Example:
    select * from emp_main where
    where trim(address <> '');
    This query doesn't give the wright results on Oracle 8i even though there are employees that have addresses
    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Hi,

    I guess your select is actually (1 "where" too much, last paranthesis moved a little to the left) :
    select * from emp_main where
    where trim(address) <> '';

    Your problem is most probably due to the fact that the expression [trim(address)] evaluates to null.

    Try

    select * from emp_main where trim(address) is not null;

    CVM.

  3. #3
    Join Date
    Oct 2003
    Posts
    20

    comparing with empty string

    Hi,
    thanks for your help.
    the problem is even if I try without trim function :
    select * from emp_main where
    where address <> '';
    I won't have any results even though as I told u before the are employees that have addresses.
    I know that is not null works but the problem is that I need to compare with an empty string.
    In fact, I want the following query that works on Sybase to work on Oracle:
    select * from emp_main where
    where address is not null and trim(address) <> '';
    thanks

  4. #4
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    Hello,
    Probably what u r trying cannot be done in oracle.
    I was facing the same problem but with sql server.

    Are u sure that there are empty strings in the table?

    cyrus

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Empty strings in oracle are stored as null, unlike Sybase & SQLServer

    If you insert '' into a varchar2 column then select * from table where column is null, it will return that record.

    So

    select * from emp_main where
    where address is not null

    should suffice in Oracle.

    If you have just spaces in address then try

    select * from emp_main where
    where trim(address) is not null

    The reason <> '' doesnt work is because in Oracle null means unknown so you are saying does address not equal unknown which will not evalute to either true or false but null in Oracle.

    Alan

  6. #6
    Join Date
    Oct 2003
    Location
    Pune
    Posts
    59
    what ever alan has said is perfectly right!!!!!!!

    Cyrus

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •