All versions: 3.11 | 3.10 | 3.9 | 3.8 | 3.7 | 3.6 | 3.5 | 3.4 | 3.3 | Development versions: 3.12 | Unsupported versions: 3.2

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();
The jOOQ Logo