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 Terry Kilshaw <te...@quantechsoftware.com> on 2006/08/09 23:24:07 UTC
case insensitive searches
Is it possible to set Derby to do case-insensitive searches? The default
seems to be case-sensitive. This would be fine as a global setting that
never needs to change.
thanks,
Terry
Re: case insensitive searches
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Hi,
Edson Carlos Ericksson Richter wrote:
> Is not possible to create a index based on function results?
Not for the moment. It is proposed as a new feature
(https://issues.apache.org/jira/browse/DERBY-455), but I don't think
anyone is working on it presently.
>
> Regards,
>
> Richter
>
>
>
>
> Terry Kilshaw escreveu:
>
>> Kristian,
>>
>> I mean like in MySQL. You configure it for case insensitive searching and
>> that's it.
>>
>> For example the following two SELECT statements return the same row,
>> whose
>> actual value is 'jdoe':
>>
>> select userID from tbl_personnel where userID = 'jdoe'
>> select userID from tbl_personnel where userID = 'JDOE'
>> Most of the feedback so far seems to indicate that although
>> cas-insensitive
>> searching can be done it Derby, it carries the penalty of not using
>> indexes.
>>
>> Terry
>>
>> -----Original Message-----
>> From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM] Sent:
>> Wednesday, August 09, 2006 3:20 PM
>> To: Derby Discussion
>> Subject: Re: case insensitive searches
>>
>> Terry Kilshaw wrote:
>>
>>
>>> Is it possible to set Derby to do case-insensitive searches? The
>>> default seems to be case-sensitive. This would be fine as a global
>>> setting that never needs to change.
>>>
>>
>>
>> Hi Terry,
>>
>> Could give us some more information on what kind of searches you are
>> talking
>> about? Most importantly, what searching mechanism is being used.
>>
>>
>>
>> Thank you,
>>
>
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
Re: case insensitive searches
Posted by Edson Carlos Ericksson Richter <ed...@mgrinformatica.com.br>.
Is not possible to create a index based on function results?
Regards,
Richter
Terry Kilshaw escreveu:
> Kristian,
>
> I mean like in MySQL. You configure it for case insensitive searching and
> that's it.
>
> For example the following two SELECT statements return the same row, whose
> actual value is 'jdoe':
>
> select userID from tbl_personnel where userID = 'jdoe'
> select userID from tbl_personnel where userID = 'JDOE'
>
> Most of the feedback so far seems to indicate that although cas-insensitive
> searching can be done it Derby, it carries the penalty of not using indexes.
>
> Terry
>
> -----Original Message-----
> From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
> Sent: Wednesday, August 09, 2006 3:20 PM
> To: Derby Discussion
> Subject: Re: case insensitive searches
>
> Terry Kilshaw wrote:
>
>> Is it possible to set Derby to do case-insensitive searches? The
>> default seems to be case-sensitive. This would be fine as a global
>> setting that never needs to change.
>>
>
> Hi Terry,
>
> Could give us some more information on what kind of searches you are talking
> about? Most importantly, what searching mechanism is being used.
>
>
>
> Thank you,
>
RE: case insensitive searches
Posted by Terry Kilshaw <te...@quantechsoftware.com>.
Kristian,
I mean like in MySQL. You configure it for case insensitive searching and
that's it.
For example the following two SELECT statements return the same row, whose
actual value is 'jdoe':
select userID from tbl_personnel where userID = 'jdoe'
select userID from tbl_personnel where userID = 'JDOE'
Most of the feedback so far seems to indicate that although cas-insensitive
searching can be done it Derby, it carries the penalty of not using indexes.
Terry
-----Original Message-----
From: Kristian.Waagan@Sun.COM [mailto:Kristian.Waagan@Sun.COM]
Sent: Wednesday, August 09, 2006 3:20 PM
To: Derby Discussion
Subject: Re: case insensitive searches
Terry Kilshaw wrote:
> Is it possible to set Derby to do case-insensitive searches? The
> default seems to be case-sensitive. This would be fine as a global
> setting that never needs to change.
Hi Terry,
Could give us some more information on what kind of searches you are talking
about? Most importantly, what searching mechanism is being used.
Thank you,
--
Kristian
>
> thanks,
>
> Terry
Re: case insensitive searches
Posted by Kristian Waagan <Kr...@Sun.COM>.
Terry Kilshaw wrote:
> Is it possible to set Derby to do case-insensitive searches? The default
> seems to be case-sensitive. This would be fine as a global setting that
> never needs to change.
Hi Terry,
Could give us some more information on what kind of searches you are
talking about? Most importantly, what searching mechanism is being used.
Thank you,
--
Kristian
>
> thanks,
>
> Terry
Re: How to modify the SQLParser to handle NULL in column definitions
?
Posted by "Bernt M. Johnsen" <Be...@Sun.COM>.
Hi,
This is a developer question which should be posted on derby-dev.
Anyway, although I know there are databases which allow you to specify
nullability by the keyword NULL (which is redundant since columns are
nullable unless given a NOT NULL constraint), this is *not* a part of
the SQL standard. The create statement below should be written
create testtable(
code int NOT NULL,
description varchar(32)
)
which would be more portable since it then would be standard compliant.
Duncan Groenewald wrote:
> Can anyone provide a pointer as to what needs to be modified in order
> for derby to handle a column definition that includes a NULL, as shown
> below:
>
> create testtable(
> code int NOT NULL,
> description varchar(32) NULL
> )
>
> I have no real idea how the parser works but figured it can't be that
> hard to be able to handle this without complaining about a syntax
> error. It looks to me like this gets done in the SQLParser.java file
> but I have not been able to figure out how it works. There is also a
> sqlgrammar.jj file which I assume is not actually used for anything
> although it looks like it might be some kind of definition file for
> SQLParser.
>
> Thanks in advance.
> Duncan
>
--
Bernt Marius Johnsen, Database Technology Group,
Staff Engineer, Technical Lead Derby/Java DB
Sun Microsystems, Trondheim, Norway
How to modify the SQLParser to handle NULL in column definitions
?
Posted by Duncan Groenewald <du...@xtra.co.nz>.
Can anyone provide a pointer as to what needs to be modified in order
for derby to handle a column definition that includes a NULL, as shown
below:
create testtable(
code int NOT NULL,
description varchar(32) NULL
)
I have no real idea how the parser works but figured it can't be that
hard to be able to handle this without complaining about a syntax
error. It looks to me like this gets done in the SQLParser.java file
but I have not been able to figure out how it works. There is also a
sqlgrammar.jj file which I assume is not actually used for anything
although it looks like it might be some kind of definition file for
SQLParser.
Thanks in advance.
Duncan
Re: case insensitive searches
Posted by Craig L Russell <Cr...@Sun.COM>.
On Aug 9, 2006, at 4:27 PM, Farukh S. Najmi wrote:
> Stephen Caine wrote:
>
>> Terry,
>>
>>> Is it possible to set Derby to do case-insensitive searches? The
>>> default seems to be case-sensitive. This would be fine as a
>>> global setting that never needs to change.
>>>
>>
>> Are you using 'starts with', 'contains' or 'equals'? These
>> operators tend to be case sensitive. Can you use 'like'? This is
>> case insensitive.
>>
>> Stephen Caine
>> Soft Breeze Systems, LLC
>
> You can also use UPPER function in your query predicate as in:
>
> ... AND UPPER(name) = 'CAINE' ....
You should be aware that using UPPER(column) or LOWER(column)
disallows the use of any indexes, so this should be a secondary
qualifier for a query, not a primary qualifier.
That is, if you want to have a case-insensitive search for name,
where that's the only search qualifier, you are going to have the
performance of a table scan, whereas if you are looking for a
customer id within some range (customer id is indexed) and
secondarily a case-insensitive search for name, that is ok.
If you want an efficient case-insensitive query, you should consider
adding another column to the table that contains the UPPER or LOWER
translation of a column. Or add a soundex [1] column for better
searches.
Craig
[1] http://en.wikipedia.org/wiki/Soundex
>
> --
> Regards,
> Farrukh
>
> Web: http://www.wellfleetsoftware.com
> Blog: http://farrukhnajmi.blogspot.com
>
Craig Russell
Architect, Sun Java Enterprise System http://java.sun.com/products/jdo
408 276-5638 mailto:Craig.Russell@sun.com
P.S. A good JDO? O, Gasp!
Re: case insensitive searches
Posted by "Farukh S. Najmi" <fa...@wellfleetsoftware.com>.
Stephen Caine wrote:
> Terry,
>
>> Is it possible to set Derby to do case-insensitive searches? The
>> default seems to be case-sensitive. This would be fine as a global
>> setting that never needs to change.
>>
>
> Are you using 'starts with', 'contains' or 'equals'? These operators
> tend to be case sensitive. Can you use 'like'? This is case insensitive.
>
> Stephen Caine
> Soft Breeze Systems, LLC
You can also use UPPER function in your query predicate as in:
... AND UPPER(name) = 'CAINE' ....
--
Regards,
Farrukh
Web: http://www.wellfleetsoftware.com
Blog: http://farrukhnajmi.blogspot.com
Re: case insensitive searches
Posted by Stephen Caine <st...@commongrnd.com>.
Terry,
> Is it possible to set Derby to do case-insensitive searches? The
> default seems to be case-sensitive. This would be fine as a global
> setting that never needs to change.
>
Are you using 'starts with', 'contains' or 'equals'? These operators
tend to be case sensitive. Can you use 'like'? This is case
insensitive.
Stephen Caine
Soft Breeze Systems, LLC