db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "geoff hendrey (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1748) Global case insensitive setting
Date Wed, 10 Mar 2010 19:23:28 GMT

    [ https://issues.apache.org/jira/browse/DERBY-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12843715#action_12843715

geoff hendrey commented on DERBY-1748:


I was just thinking it would be nice, now that you've shown this works, to have a pointer
to a derby doc section on ""how to make your database case insensitive". If the details are
hidden in a section on character collation, I don't think the average user of Derby will ever
stumble upon it. Basically, out of laziness, I thought it would be good for you to send an
email to the derby user group explaining how to make your database case insensitive. It's
been a much desired feature.


http://nextdb.net - RESTful Relational Database

--- On Wed, 3/10/10, Gunnar Grim (JIRA) <jira@apache.org> wrote:

From: Gunnar Grim (JIRA) <jira@apache.org>
Subject: [jira] Commented: (DERBY-1748) Global case insensitive setting
To: geoff_hendrey@yahoo.com
Date: Wednesday, March 10, 2010, 12:13 AM

    [ https://issues.apache.org/jira/browse/DERBY-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12843473#action_12843473

Gunnar Grim commented on DERBY-1748:

Not sure what you mean Geoff, so I'll assume you are thinking of string comparisons 
in SQL.

All string comparisons I've tested become case insensitive with a database 
that uses TERRITORY_BASED:SECONDARY collation. I've tested the following

name LIKE 'a%'
 matches both "Adam" and "adam"

name = 'adam'
 matches both "Adam" and "adam"

name BETWEEN 'a' AND 'c'
 matches both "Baker" and "baker"

name >= 'a' AND name <= 'c'
 matches both "Baker" and "baker"

Using a varchar column as a primary key will consider 'Adam' and 'adam' as duplicates.
JOIN's compare case insensitively.


This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

> Global case insensitive setting
> -------------------------------
>                 Key: DERBY-1748
>                 URL: https://issues.apache.org/jira/browse/DERBY-1748
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Terry
>         Attachments: collation-strength.diff
> By default MySQL is case insensitive in its string comparisons, as you can see from the
MySQL docs shown below. Similar functionality is available in Sybase iAnywhere and in SQLServer.
I'd like the same to be true for Derby.
> What, I wonder, are chances of that?
> I am aware that functions could be used to force comparisons in upper case but that subverts
the indexes and makes searches unacceptably long.
> If you were to ask people you might find that this is a feature whose abscence is causing
many to look elsewhere.
> thanks for all the great work,
> Terry
> The MySQL Docs say:
> -------- start quote
>  By default, MySQL searches are not case sensitive (although there are some character
sets that are never case insensitive, such as czech). This means that if you search with col_name
LIKE 'a%', you get all column values that start with A or a. If you want to make this search
case sensitive, make sure that one of the operands has a case sensitive or binary collation.
For example, if you are comparing a column and a string that both have the latin1 character
set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs
or latin1_bin collation. For example:
> col_name COLLATE latin1_general_cs LIKE 'a%'
> col_name LIKE 'a%' COLLATE latin1_general_cs
> col_name COLLATE latin1_bin LIKE 'a%'
> col_name LIKE 'a%' COLLATE latin1_bin
> If you want a column always to be treated in case-sensitive fashion, declare it with
a case sensitive or binary collation. See Section 13.1.5, "CREATE TABLE Syntax". 
>     By default, the search is performed in case-insensitive fashion. In MySQL 4.1 and
up, you can make a full-text search by using a binary collation for the indexed columns. For
example, a column that has a character set of latin1 can be assigned a collation of latin1_bin
to make it case sensitive for full-text searches.
> --------------- end quote

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message