Wednesday, 11 February 2009

Case-insensitive ordering using HSQLDB

We've recently begun the process of testing our app on Postgres, and came across a problem where our 'order' statements were returning results in a different order to HSQLDB.

Postgres was ignoring the case of items, whereas HSQLDB was not - so if I were to order the strings "Al" and "AM", HSQLDB would show "AM" before "Al", but Postgres would show "Al" before "AM".

Given that the behaviour of Postgres (case-insensitive ordering) was the desired behaviour, we wanted to get HSQLDB to do the same. After a lot of searching any many dead ends, we found that you simply use ".ignoreCase()" after an order criteria:

List books = Book.withCriteria() {
ilike('authorName', "%${searchKey}%")
order('authorName', 'asc').ignoreCase()

No comments: