You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Thomas Vatter <th...@network-inventory.de> on 2006/02/28 11:51:56 UTC

like '%something'

this is working
    select col from table where lower(col) like '%something%'
this is not
    select col from table where lower(col) like '%something'
try it with value in database = XXXsomething
seems like derby auto generates trailing blanks

tom


Re: like '%something'

Posted by Thomas Vatter <th...@network-inventory.de>.
I understand, I'll change the code. the code had worked with other dbms, 
but no problem to change it

tom

Knut Anders Hatlen wrote:

>Thomas Vatter <th...@network-inventory.de> writes:
>
>  
>
>>this is working
>>    select col from table where lower(col) like '%something%'
>>this is not
>>    select col from table where lower(col) like '%something'
>>try it with value in database = XXXsomething
>>seems like derby auto generates trailing blanks
>>    
>>
>
>You should use VARCHAR instead of CHAR. CHAR is fixed length (with
>trailing spaces) and VARCHAR is variable length.
>
>ij> create table t (text varchar(20));
>0 rows inserted/updated/deleted
>ij> insert into t values ('something'), ('something2');
>2 rows inserted/updated/deleted
>ij> select * from t where text like '%something';
>TEXT                
>--------------------
>something           
>
>1 row selected
>ij> create table t2 (text char(20));
>0 rows inserted/updated/deleted
>ij> insert into t2 values ('something'), ('something2');
>2 rows inserted/updated/deleted
>ij> select * from t2 where text like '%something';
>TEXT                
>--------------------
>
>0 rows selected
>
>  
>


Re: like '%something'

Posted by Thomas Vatter <th...@network-inventory.de>.
yes, with varchar it works fine


Knut Anders Hatlen wrote:

>Thomas Vatter <th...@network-inventory.de> writes:
>
>  
>
>>this is working
>>    select col from table where lower(col) like '%something%'
>>this is not
>>    select col from table where lower(col) like '%something'
>>try it with value in database = XXXsomething
>>seems like derby auto generates trailing blanks
>>    
>>
>
>You should use VARCHAR instead of CHAR. CHAR is fixed length (with
>trailing spaces) and VARCHAR is variable length.
>
>ij> create table t (text varchar(20));
>0 rows inserted/updated/deleted
>ij> insert into t values ('something'), ('something2');
>2 rows inserted/updated/deleted
>ij> select * from t where text like '%something';
>TEXT                
>--------------------
>something           
>
>1 row selected
>ij> create table t2 (text char(20));
>0 rows inserted/updated/deleted
>ij> insert into t2 values ('something'), ('something2');
>2 rows inserted/updated/deleted
>ij> select * from t2 where text like '%something';
>TEXT                
>--------------------
>
>0 rows selected
>
>  
>

-- 
Mit freundlichen Grüßen 
Vatter
 
Network Inventory Software
Sun Microsystems Principal Partner

www.network-inventory.de
Tel. 030-79782510
E-Mail thomas.vatter@network-inventory.de


Re: like '%something'

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Thomas Vatter <th...@network-inventory.de> writes:

> this is working
>     select col from table where lower(col) like '%something%'
> this is not
>     select col from table where lower(col) like '%something'
> try it with value in database = XXXsomething
> seems like derby auto generates trailing blanks

You should use VARCHAR instead of CHAR. CHAR is fixed length (with
trailing spaces) and VARCHAR is variable length.

ij> create table t (text varchar(20));
0 rows inserted/updated/deleted
ij> insert into t values ('something'), ('something2');
2 rows inserted/updated/deleted
ij> select * from t where text like '%something';
TEXT                
--------------------
something           

1 row selected
ij> create table t2 (text char(20));
0 rows inserted/updated/deleted
ij> insert into t2 values ('something'), ('something2');
2 rows inserted/updated/deleted
ij> select * from t2 where text like '%something';
TEXT                
--------------------

0 rows selected

-- 
Knut Anders