Re: Checking for empty string in a query

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)