John English <[email protected]> writes:
> 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.
Is my_col a char column or is it a varchar column? I see what you
describe with char, but not with varchar.
> 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?
It might be caused by padding of CHAR columns. For example:
ij> create table t(x varchar(10), y char(10));
0 rows inserted/updated/deleted
ij> insert into t values ('','');
1 row inserted/updated/deleted
ij> select length(x), length(y) from t;
1 |2
-----------------------
0 |10
1 row selected
Note that even though the value inserted into the CHAR(10) column is
empty, the value in the table has length 10, so it will match the LIKE
pattern '%_%'.
The reason why it works as expected with my_col='', is that the shorter
operand ('') is padded to match the length of the longer operand, per
http://db.apache.org/derby/docs/10.9/ref/rrefsqlj13733.html.
--
Knut Anders
