Re: Checking for empty string in a query

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1080076227.543315_at_yasure...
| D. Alvarado wrote:
| > I'm running Oracle 8.1.7 for Solaris.
| >
| > I'm having a problem with NULL and empty string equivalence. Here are
| > some rows from my table:
| >
| > SITE ACCESS_TYPE
| > ---- / -----------
| > NULL 3
| > webstats 4
| > temp 3
| >
| > but if I run the following query, I get no result:
| >
| > SELECT * FROM A WHERE SITE = ''
| >
| > however if I run
| >
| > SELECT * FROM A WHERE SITE IS NULL
| >
| > I get the proper row. The problem is, I would like to have a single
| > statement for my JDBC call. Right now, that statement is
| >
| > SELECT * FROM A WHERE SITE = ?
| >
| > but because of the first reason I mentioned, this doesn't work when
| > the "?" is an empty string. Other than creating an "if-else"
| > conditional, is there any single query I can write that will encompass
| > all possible values for SITE? Thanks - Dave
|
| How about something based on this?
|
| SELECT *
| FROM test
| WHERE DECODE(site, NULL, 1, access_type) = 1;
|
| --
| Daniel Morgan
| http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
| http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
| damorgan_at_x.washington.edu
| (replace 'x' with a 'u' to reply)
|

for performance, do the if-then-else in your code to select an appropriate statement, since a decode (or an OR clause in the sql) will be less likely to use an available index -- or be sure to use a function-based index on the compensating expression you include in the WHERE clause

but i think the sql that would transform an IS NULL search so it could be handled in the same code as a basic comparison should probably look more like this:

select *
from test
where nvl(site,-1) = nvl(:var,-1)

assuming '-1' is an impossible value for 'site'

;-{ mcs Received on Tue Mar 23 2004 - 16:55:53 CST