Home » SQL & PL/SQL » SQL & PL/SQL » Comparing null strings (11g)
Comparing null strings [message #350344] Wed, 24 September 2008 15:33 Go to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
I'm trying to select records based on the value of a varchar field. The problem is when I try and match empty strings.

As an example, this will never match records where field is NULL.

SELECT * 
FROM   my_table 
WHERE  field = :value;


Because the proper syntax to do that would be:

SELECT * 
FROM   my_table 
WHERE  field is NULL;


To try and match null and non-null values I could do something like this:

SELECT *
FROM   my_table
WHERE  field = :value
        OR (:value IS NULL 
            AND field IS NULL );


My question is, is there a better way to do this?
Re: Comparing null strings [message #350348 is a reply to message #350344] Wed, 24 September 2008 15:40 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
Use NVL.
Re: Comparing null strings [message #350352 is a reply to message #350348] Wed, 24 September 2008 15:58 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Could you elaborate? Are you implying doing something like this?

SELECT * FROM my_table
WHERE nvl(field, 'x') = nvl(:1, 'x');

Re: Comparing null strings [message #350429 is a reply to message #350352] Thu, 25 September 2008 00:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, this is that.

Regards
Michel
Re: Comparing null strings [message #350463 is a reply to message #350352] Thu, 25 September 2008 02:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What you have to watch with that solution is that it will give a match if the bind value is null and there is a record witha value 'x'
Re: Comparing null strings [message #350479 is a reply to message #350352] Thu, 25 September 2008 03:30 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
It is better to explicitly check if both values are null. This avoids cases like 'x' <> nvl(null,'x').

Bye Alessandro
Re: Comparing null strings [message #350568 is a reply to message #350463] Thu, 25 September 2008 08:00 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
JRowbottom wrote on Thu, 25 September 2008 03:40
What you have to watch with that solution is that it will give a match if the bind value is null and there is a record witha value 'x'


Yep, and in such case I would use something like a carat (^) or pound sign (#) or something else I know cannot be a value in the column.
Re: Comparing null strings [message #350620 is a reply to message #350568] Thu, 25 September 2008 10:57 Go to previous messageGo to next message
psk1
Messages: 30
Registered: August 2008
Location: Seattle, WA
Member
Is there any advantage to using nvl over explicitly checking for null values?
Re: Comparing null strings [message #350622 is a reply to message #350620] Thu, 25 September 2008 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Less letters to type.

Regards
Michel
Re: Comparing null strings [message #350654 is a reply to message #350568] Thu, 25 September 2008 16:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Hah! You must have more competent users than I've had to put up with.....

I used to have a variable defined in apackage header that was something like '%*)(*%' and i'd refer to this in the NVLs - that way if one of them accidentally typed that into a field, at least it was only a 30 second job to change the system.

You could use
AND (field = :value OR field||:value IS NULL)
Slightly less typing.
Re: Comparing null strings [message #350701 is a reply to message #350344] Thu, 25 September 2008 23:34 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Another alternative, you can avoid the problem of default values by using DECODE. Consider this example:

SQL> select *
  2  from dual
  3  where decode(null,null,1,0) = 1
  4  /

D
-
X

SQL> 


If null is not equal null then how did we get a row?

DECODE is one of the few functions that looks at null as simply another value. We can expoit this in an situation where we want null=null to work like any other equality test.

Good luck, Kevin
Re: Comparing null strings [message #350717 is a reply to message #350701] Fri, 26 September 2008 00:51 Go to previous message
Michel Cadot
Messages: 64125
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
THe main difference is that optimizer knows what is NVL and can use indexes and null/not null constraints to rewrite the query when it can't do it with DECODE (at least till now).

Regards
Michel
Previous Topic: Trim
Next Topic: how can i connect a paradox database in a plsql block?
Goto Forum:
  


Current Time: Wed Dec 07 06:43:15 CST 2016

Total time taken to generate the page: 0.14647 seconds