db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: Selecting empty columns?
Date Wed, 23 Jan 2013 13:13:46 GMT
John English <john.foreign@gmail.com> 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

Mime
View raw message