1

I don't have a lot of experience with Oracle, so forgive me if this is obvious:

We have an Employee table with a field named EMPLOYEE_ADDRESS defined as char(60 BYTE)

We want a simple where to find out anyone without anything in their email address field.

The following two queries return no results:

Select * from employee where email_address is null;  -- fails because the records aren't null, they're empty;

select * from employee where email_address = '';

So I thought, maybe the issue is that, because of the field definition, I need 60 blank spaces, so I tried this and it returned the expected results:

select * from employee where email_address  = '                                                            ';

That works, but it seems dumb and I don't want to put that into production code. So I went on to try to trim the results something like one of the following three queries:

select * from employee where TRIM(email_address) = '';
select * from employee where RTRIM(email_address) = '';
select * from employee where LTRIM(email_address) = '';

None of those worked, so feeling frustrated and just having to know if it would work, I tired this to see if trimming had any effect at all:

select * from employee where ltrim(email_address) || 'a'  = 'a';

That worked. (Reaction.. wat?)

So I'm wondering first of all, why using the various trim functions didn't work, and secondly, how can I construct the query so that it's not literally looking for 60 blank spaces?

David
  • 72,686
  • 18
  • 132
  • 173

2 Answers2

2
Select * from employee where NVL(trim(email_address), '*') <> '*';
Art
  • 5,616
  • 1
  • 20
  • 22
1

This will work

select * from employee where TRIM(email_address) is null

The reason why the above works and this doesn't

select * from employee where TRIM(email_address) = ''

is because Oracle treats the zero length text as null.

When you want to work with empty cells you have to use NVL in order to give it a default value so the equation can do what it's supposed to.

See this for more Why does Oracle 9i treat an empty string as NULL?

Community
  • 1
  • 1
Alkis Kalogeris
  • 17,044
  • 15
  • 59
  • 113
  • I do not care about scores, I simply bored... The only correct solution to this is NVL(). It is a bulletproof, so to speak... – Art Feb 17 '15 at 20:23
  • 1
    Actually, the correct solution would be to define the field as `varchar2(60)`. The only time `char(x)` should be used is if you know the data will always be x characters in length. Like `char(2)` to contain the two-letter State abbreviations: 'AL', 'AK', etc. or `char(1)` to contain 'Y' or 'N'. Addresses will vary widely in length even if it consists only of number and street name. So the field containing it should be able to vary in length. – TommCatt Feb 18 '15 at 05:18
  • To alkis: Sure I will explain, although it is already explained in my answer. Please copy/paste to see results: SELECT * FROM scott.emp WHERE trim(to_char(comm)) = ''; SELECT * FROM scott.emp WHERE NVL(to_char(comm), '*') = '*'; Strangely the asterisk in second query does not display or copies...The second query uses asterisk on both sides, similar to my answer below. – Art Feb 19 '15 at 14:02
  • Won't this work? `SELECT * FROM scott.emp WHERE trim(to_char(comm)) is null;` . Because that's what I proposed. And it does. Instead of putting a default value where the result is empty with NVL, I proposed to check for `is null`, since Oracle treats empty strings as null. I've used this NVL trick in the past because I needed the equation (the value I was searching against was a dynamic parameter, so I wasn't checking only for empty or null). But in this case using `NVL` , when the OP only wants the empty results, just introduces overhead. – Alkis Kalogeris Feb 19 '15 at 14:07
  • Yes, SELECT * FROM scott.emp WHERE trim(to_char(comm)) is null; But I still think the NVL() is always best. – Art Feb 19 '15 at 18:24