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 10.8.1.2, FWIW.
TIA,
--
John English
