Available in versions: Dev (3.19) | Latest (3.18) | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10 | 3.9
Case sensitivity with strings
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
Most databases allow for specifying a COLLATION which allows for re-defining the ordering of string values. By default, ASCII, ISO, or Unicode encodings are applied to character data, and ordering is applied according to the respective encoding.
Sometimes, however, certain queries like to ignore parts of the encoding by treating upper-case and lower-case characters alike, such that ABC = abc, or such that ABC, jkl, XyZ are an ordered list of strings (case-insensitively).
For these ad-hoc ordering use-cases, most people resort to using LOWER() or UPPER() as follows:
-- Case-insensitive filtering: SELECT * FROM BOOK WHERE upper(TITLE) = 'ANIMAL FARM' -- Case-insensitive ordering: SELECT * FROM AUTHOR ORDER BY upper(FIRST_NAME), upper(LAST_NAME)
// Case-insensitive filtering:
create.selectFrom(BOOK)
.where(upper(BOOK.TITLE).eq("ANIMAL FARM")).fetch();
// Case-insensitive ordering:
create.selectFrom(AUTHOR)
.orderBy(upper(AUTHOR.FIRST_NAME), upper(AUTHOR.LAST_NAME))
.fetch();
Feedback
Do you have any feedback about this page? We'd love to hear it!