[
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:
--------------------------------------
Hi,
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.
-geoff
--
http://nextdb.net - RESTful Relational Database
http://www.nextdb.net/wiki/en/REST
--- On Wed, 3/10/10, Gunnar Grim (JIRA) <[email protected]> wrote:
From: Gunnar Grim (JIRA) <[email protected]>
Subject: [jira] Commented: (DERBY-1748) Global case insensitive setting
To: [email protected]
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.
-Gunnar
--
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.
