Differences between LIKE and equal (=) comparisons

When you use locale-based collation, the comparisons can return different results when you use the LIKE and equal (=) operators.

For example, suppose that the Derby database is set to use a locale where the character 'z' has the same collation elements as 'xy'. Consider the following two WHERE clauses:

  1. WHERE 'zcb' = 'xycb'
  2. WHERE 'zcb' LIKE 'xy_b'

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

For WHERE clause 2, Derby returns FALSE, because the collation element for the character 'z' does not match the collation element for the character 'x'. In addition, when a metacharacter such as an underscore is used with the LIKE operator, the metacharacter counts for one character in the string value. A clause such as WHERE 'xycb' LIKE '_cb' returns FALSE, because 'x' is compared to the metacharacter '_' and 'y' does not match 'c'.

Related concepts
How collation works in Derby
Locale-based collation
Database connection URL attributes that control collation
Examples of case-sensitive and case-insensitive string sorting
Related tasks
Creating a database with locale-based collation
Creating a case-insensitive database
Creating a customized collator