| Comparing null strings [message #350344] |
Wed, 24 September 2008 15:33  |
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 #350463 is a reply to message #350352] |
Thu, 25 September 2008 02:40  |
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 #350568 is a reply to message #350463] |
Thu, 25 September 2008 08:00 |
joy_division
Messages: 4963
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 #350654 is a reply to message #350568] |
Thu, 25 September 2008 16:47 |
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 useAND (field = :value OR field||:value IS NULL) |
|
|
|
|
|
| Re: Comparing null strings [message #350717 is a reply to message #350701] |
Fri, 26 September 2008 00:51 |
 |
Michel Cadot
Messages: 68761
Registered: March 2007
Location: Saint-Maur, France, https...
|
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
