[
https://issues.apache.org/jira/browse/DERBY-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12539654
]
Daniel John Debrunner commented on DERBY-1748:
----------------------------------------------
One potentially easy way to provide this functionality would be to expand the
values supported by the collation JDBC attribute.
Today it supports UCS_BASIC (fixed collation based upon Unicode codepoint) or
TERRITORY_BASED (collation using the locale (territory) of the database and
collation provided by a RuleBasedCollator object obtained using the locale. The
collation is set & fixed at create database time.
The TERRITORY_BASED could be expanded to allow setting the strength of the
RuleBasedCollator being used, e.g.
TERRITORY_BASED - default strength
TERRITORY_BASED:PRIMARY - strength set using
collator.setStrength(Collator.PRIMARY)
TERRITORY_BASED:SECONDARY - strength set using
collator.setStrength(Collator.SECONDARY)
TERRITORY_BASED:TERTIARY - strength set using
collator.setStrength(Collator.TERTIARY )
TERRITORY_BASED:IDENTICAL - strength set using
collator.setStrength(Collator.IDENTICAL)
This allows some user control of the collation and for some (most) languages
will provide case-insensitive searches.
E.g. according to the javadoc for java.text.Collator with a locale of en_US
then TERRITORY_BASED:PRIMARY will give case insensitive searches.
Apart from testing I think this would be around 20-30 lines of code:
a) code to parse the new collation attribute values
b) new constants for collation with strength (four new constants)
c) code to set the strength based upon those constants.
> Global case insensitive setting
> -------------------------------
>
> Key: DERBY-1748
> URL: https://issues.apache.org/jira/browse/DERBY-1748
> Project: Derby
> Issue Type: New Feature
> Components: SQL
> Reporter: Terry
>
> 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.
