Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1564160112.573721269931828098.JavaMail.jira@brutus.apache.org>
Date: Tue, 30 Mar 2010 06:50:28 +0000 (UTC)
From: "Gunnar Grim (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (DERBY-1748) Global case insensitive setting
In-Reply-To: <6394134.1156285453855.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


    [ https://issues.apache.org/jira/browse/DERBY-1748?page=3Dcom.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1285=
1288#action_12851288 ]=20

Gunnar Grim commented on DERBY-1748:
------------------------------------

The Wikipedia entry on Collation references this article: http://www.unicod=
e.org/reports/tr10/

For the normal user I believe it is sufficient to know that the strength va=
lues PRIMARY, SECONDARY and TERTIARY handle differences in base characters,=
 accents and case, respectively. If you know your own language you will kno=
w what that means. For example, anyone who knows Swedish will know that the=
 letters A and =C3=84 are different even with PRIMARY strength. The dots ar=
e not an accent in Swedish although in another language they may well be.

Therefore, I think that the text I suggested for the dev guide is detailed =
enough. Perhaps you could add a recommendation to use PRIMARY if you want D=
erby to behave like MySQL, MS SQL Server and probably most other DBMS'es do=
 by default.

> 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
>            Assignee: Kim Haase
>         Attachments: collation-strength-1.diff, collation-strength-2.diff=
, collation-strength.diff, devguide.txt, refman.txt, remove_dead_code.diff
>
>
> By default MySQL is case insensitive in its string comparisons, as you ca=
n see from the MySQL docs shown below. Similar functionality is available i=
n 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 cas=
e 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 abs=
cence 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 so=
me character sets that are never case insensitive, such as czech). This mea=
ns that if you search with col_name LIKE 'a%', you get all column values th=
at 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 lat=
in1 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, decl=
are it with a case sensitive or binary collation. See Section 13.1.5, "CREA=
TE TABLE Syntax".=20
>     By default, the search is performed in case-insensitive fashion. In M=
ySQL 4.1 and up, you can make a full-text search by using a binary collatio=
n for the indexed columns. For example, a column that has a character set o=
f latin1 can be assigned a collation of latin1_bin to make it case sensitiv=
e for full-text searches.
> --------------- end quote

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