Home » SQL & PL/SQL » SQL & PL/SQL » Check for empty and null
Check for empty and null [message #340186] Mon, 11 August 2008 18:43 Go to next message
evergrean
Messages: 15
Registered: February 2008
Junior Member
I transferring sql from MS Access to Oracle 9i.

In Access I have this:
select * from tableOne where firstname <> ''


I assume that is equal to this in Oracle:
select * from tableOne where firstname is not null


I also need to check for blank space because the firstname field can have data that was deleted and I need to check if it is empty. Would my sql work where it checks for null and if it is empty?
Re: Check for empty and null [message #340189 is a reply to message #340186] Mon, 11 August 2008 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

I am sorry to see that SQL is now broken for you precluding you from testing what works & what does.

Please be patient which repairs are underway.
Additional information will be presented as it becomes available.

By the way, from your perspective what is the different between a 'blank space' and a non-blank space?

[Updated on: Mon, 11 August 2008 18:54] by Moderator

Report message to a moderator

Re: Check for empty and null [message #340196 is a reply to message #340186] Mon, 11 August 2008 21:05 Go to previous messageGo to next message
TheSingerman
Messages: 49
Registered: April 2008
Location: Brighton, Michigan
Member
Your question suggests that you are aware of the following trap, but I want to make sure you are 100% aware of it: Oracle uses the empty string to represent a SQL null. To put it another way, there is no way in Oracle to distinguish between an empty string an a SQL null.

In other words, Anacedent is asking you a Very Important Question.
Re: Check for empty and null [message #340278 is a reply to message #340186] Tue, 12 August 2008 03:20 Go to previous messageGo to next message
magic
Messages: 5
Registered: July 2007
Location: Slovakia
Junior Member
it's depend what do you mean with "blank space" ?

1) "blank space"=NULL
or
2) "blank space"="space/spaces with no other chars"

??

if 1) then:

select * from mytab where col1 is NULL;

you get rows where col1 is NULL only

if 2) then:

select * from mytab
where decode(trim(col1),NULL,NULL,col1) is NULL;


you get rows where col1 is NULL and rows contains one or more spaces ...

Re: Check for empty and null [message #340298 is a reply to message #340278] Tue, 12 August 2008 04:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Isn't
decode(trim(col1),NULL,NULL,col1) is NULL
the same as
TRIM(col11) IS NULL
Re: Check for empty and null [message #340312 is a reply to message #340298] Tue, 12 August 2008 05:19 Go to previous message
magic
Messages: 5
Registered: July 2007
Location: Slovakia
Junior Member
Yes, it's same ... Cool
Previous Topic: How to check space at varchar2 column
Next Topic: cursor fetching
Goto Forum:
  


Current Time: Wed Dec 07 04:59:54 CST 2016

Total time taken to generate the page: 0.09440 seconds