Collation examples

Differences between LIKE and equal (=) comparisons

When you use territory-based collation, the comparisons -can return different results when you use the LIKE and equal +can return different results when you use the LIKE and equal (=) operators. For example, suppose that the database is set to use a territory where the character 'z' has same collation elements as 'xy'. Consider the following two WHERE clauses:

WHERE 'zcb' = 'xycb'
WHERE 'zcb' LIKE 'xy_b
WHERE 'zcb' LIKE 'xy_b'

For WHERE clause 1, returns TRUE because the collation elements for the entire string 'zcb' will match the collation elements of the entire string 'xycb'.

For WHERE clause Modified: db/derby/docs/trunk/src/devguide/tdevdvlpcollation.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/devguide/tdevdvlpcollation.dita?rev=932066&r1=932065&r2=932066&view=diff ============================================================================== --- db/derby/docs/trunk/src/devguide/tdevdvlpcollation.dita (original) +++ db/derby/docs/trunk/src/devguide/tdevdvlpcollation.dita Thu Apr 8 19:12:59 2010 @@ -28,7 +28,7 @@ when you create the database.collation territory-based -.

You can use the collation and territory attributes +

You can use the collation and territory attributes to specify territory-based collation. This type of collation applies only to user-defined tables. The system tables use the Unicode codepoint collation.

The collation attribute can be specified @@ -41,5 +41,25 @@ and the TERRITORY_BASED value for the

For example:

jdbc:derby:MexicanDB;create=true;territory=es_MX;collation=TERRITORY_BASED +Creating a case-insensitive database +

The collation value TERRITORY_BASED uses the default +collation strength for the locale, usually TERTIARY, which will consider +character case significant in searches and comparisons. To make the database +use case-insensitive searches, specify an explicit strength with the +collation attribute. The strength name is appended to +TERRITORY_BASED with a colon to separate them.

For example:

+jdbc:derby:SwedishDB;create=true;territory=sv_SE;collation=TERRITORY_BASED:PRIMARY +

With strength PRIMARY, the characters 'A' and 'a' will be considered equal, +as well as 'a' with no accent and 'a' with a grave accent. (This behavior is +commonly the default with many other databases.) To make searches +respect differences in accent, use strength SECONDARY.

The exact interpretation of the strength part of the attribute depends upon +the locale.

For more information, see + and the documentation of +the collation attribute in the +.

+ Modified: db/derby/docs/trunk/src/ref/rrefattribcollation.dita URL: http://svn.apache.org/viewvc/db/derby/docs/trunk/src/ref/rrefattribcollation.dita?rev=932066&r1=932065&r2=932066&view=diff ============================================================================== --- db/derby/docs/trunk/src/ref/rrefattribcollation.dita (original) +++ db/derby/docs/trunk/src/ref/rrefattribcollation.dita Thu Apr 8 19:12:59 2010 @@ -30,27 +30,61 @@ limitations under the License.

Function

The collation attribute is an optional attribute that specifies whether collation is based on the territory specified for the database or Unicode codepoint collation. The valid -values for the collation attribute are: TERRITORY_BASED and -UCS_BASIC. The default collation is Unicode codepoint collation (UCS_BASIC).

collation attribute are:

UCS_BASIC: Unicode codepoint collation. This value is the default.
TERRITORY_BASED: Based on the language specified with the territory attribute. The default +collation strength for the locale is used. The default for + is commonly TERTIARY, +in which character case is significant in searches and comparisons.
TERRITORY_BASED:PRIMARY: Territory based with collation strength PRIMARY. Specify this value to make + behave similarly to +many other databases, for which PRIMARY is commonly the default.
TERRITORY_BASED:SECONDARY: Territory based with collation strength SECONDARY.
TERRITORY_BASED:TERTIARY: Territory based with collation strength TERTIARY.
TERRITORY_BASED:IDENTICAL: Territory based with collation strength IDENTICAL.

+The collation attribute can be specified only when you create a database. You cannot specify this attribute on an existing -database or when you upgrade a database.

If you include the collation attribute -with the value TERRITORY_BASED when you create the database, -the collation is based on the language and country codes that you specify +database or when you upgrade a database. +

If you specify the collation attribute with the value +TERRITORY_BASED, or one of its variants with a specific collation strength, the +collation is based on the language and country codes that you specify with the territory attribute.

If you do not specify the territory attribute when you create the database, uses the java.util.Locale.getDefault method to determine the current value of the default locale for this instance of the Java Virtual Machine (JVM).

The collation attribute applies only to user-defined tables. The system tables use the Unicode codepoint -collation.

+collation. +

For information on how + handles collation, see +"Creating a database with territory-based collation" and "Character-based +collation in " in the +.