Hi Geoff,
Some comments inline...
Geoff hendrey wrote:
Two questions:
1) does this mean that '=' and 'LIKE' comparisons can be case insensitive?
Yes. Bear in mind, however, that the LIKE optimizations do not work on
territory-based collations today. See
http://issues.apache.org/jira/browse/DERBY-3854
2) is it possible to switch between case-sensitive and
case-insensitive comparisons on the fly?
Not with indexed support, that I'm aware of. You can always wrap your
column references with normalizing functions like UPPER and get the
semantics (but not necessarily the performance) that you want. For more
speculation about how to handle these issues, please see
http://www.nabble.com/ORDER-BY-and-greek-characters-td20748193.html#a20748193
Hope this helps,
-Rick
-geoff
“The Americans would be less dangerous if they had a regular army.”
– British General Frederick Haldimand, Boston, 1776
*From:* Knut Anders Hatlen (JIRA) <[EMAIL PROTECTED]>
*To:* [EMAIL PROTECTED]
*Sent:* Friday, December 5, 2008 7:48:44 AM
*Subject:* [jira] Commented: (DERBY-1748) Global case insensitive setting
[
https://issues.apache.org/jira/browse/DERBY-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12653836#action_12653836
<https://issues.apache.org/jira/browse/DERBY-1748?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12653836#action_12653836>
]
Knut Anders Hatlen commented on DERBY-1748:
-------------------------------------------
What Dan suggests in his comment (02/Nov/07) could be achieved with no
changes in Derby if you're using Java SE 6. Define your own
java.text.spi.CollatorProvider which returns a Collator on which you
have called setStrength() and tell Derby to use that collator
(territory=<your-custom-locale>;collation=TERRITORY_BASED;create=true).
I just posted an example here:
http://blogs.sun.com/kah/entry/user_defined_collation_in_apache
> 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.
