db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English <john.fore...@gmail.com>
Subject Selecting empty columns?
Date Wed, 23 Jan 2013 12:02:03 GMT
I want to select all rows from a table where a varchar column is a zero-length 
string. Of course this works:
   SELECT * FROM my_table WHERE my_col='';
but I would have expected this to work too:
   SELECT *  FROM my_table WHERE my_col NOT LIKE '%_%';
The first query returns all the expected rows; the second returns zero rows.

I expected that the second query would return all rows containing a value for 
my_col which did not contain any single character anywhere in the value.

This arises in the situation where I want users to be able to leave a field 
blank in a form if they want all rows, and fill in the field with a value to be 
used to select a subset of rows. I use "~" to mean "not" and so selecting the 
value "~_" generates the second query above.

Is there something I'm misunderstanding here?

I'm using Derby, FWIW.

John English

View raw message