# How to use collate in JPQL

**URL:** <https://discourse.hibernate.org/t/how-to-use-collate-in-jpql/9542>\
**Category:** Hibernate ORM\
**Created:** [May 20, 2024, 2:54pm UTC](https://discourse.hibernate.org/t/how-to-use-collate-in-jpql/9542 "2024-05-20T14:54:30Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kevinm416](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kevinm416/32/2892_2.png) [@kevinm416](https://discourse.hibernate.org/u/kevinm416)\
**Post date:** [May 20, 2024, 2:54pm UTC](https://discourse.hibernate.org/t/how-to-use-collate-in-jpql/9542/1 "2024-05-20T14:54:30Z")

</div>

I want to use collation in JPQL, and it seems like it should work based on the Hibernate User Guide. Collate is listed as a string function, but it’s also the only string function that doesn’t get a full working example: [Hibernate ORM 6.0.0.CR1 User Guide](https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html#_collate).

What is shown in the user guide is `collate(p.name as collation)`, whereas the other functions from the same table are called with parameters separated by commas, not the `AS` keyword. I tried it both ways, and got the following results:

SQL Migration:

```auto
CREATE COLLATION display_id_collation (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ka-shifted-kn');

```

**Attempt #1**

```auto
SELECT new com.test.Result(i, i.customer, pi)
FROM Invoice i
INNER JOIN PaidInvoice pi ON i.id = pi.invoice.id
ORDER BY COLLATE(i.displayId as display_id_collation)

```

Error:

```auto
Caused by: o.s.d.j.r.q.BadJpqlGrammarException: Line 6:35 mismatched input 'as' expecting {',', ')', '+', '-', '/', '||', '[', '.', '*', BY, DAY, EPOCH, HOUR, MINUTE, MONTH, NANOSECOND, QUARTER, SECOND, WEEK, YEAR}; Bad JPQL grammar [...]
        at o.s.d.j.r.q.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39)

```

**Attempt #2**

```auto
SELECT new com.test.Result(i, i.customer, pi)
FROM Invoice i
INNER JOIN PaidInvoice pi ON i.id = pi.invoice.id
ORDER BY COLLATE(i.displayIdSearch, display_id_collation) ASC

```

Error:

```auto
Caused by: o.h.q.SemanticException: Could not interpret path expression 'display_id_collation'
        at o.h.q.h.i.BasicDotIdentifierConsumer$BaseLocalSequencePart.resolvePathPart(BasicDotIdentifierConsumer.java:255)

```

**Attempt #2.1**

```auto
SELECT new com.test.Result(i, i.customer, pi)
FROM Invoice i
INNER JOIN PaidInvoice pi ON i.id = pi.invoice.id
ORDER BY COLLATE(i.displayIdSearch, 'display_id_collation') ASC

```

Error:

```auto
Caused by: o.h.q.s.p.f.FunctionArgumentException: Parameter 2 of function 'collate()' has type 'COLLATION', but argument is of type 'java.lang.Object'
        at o.h.q.s.p.f.ArgumentTypesValidator.throwError(ArgumentTypesValidator.java:303)
        at o.h.q.s.p.f.ArgumentTypesValidator.validate(ArgumentTypesValidator.java:120)

```

The error message from attempt #2.1 seems to suggest it could work if I can pass in an object of type ‘COLLATION’, but I couldn’t find any documentation for how to do that.

---

<div class="post-metadata">

**Author:** ![mbladel](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/mbladel/32/2128_2.png) [@mbladel](https://discourse.hibernate.org/u/mbladel)\
**Post date:** [May 20, 2024, 3:04pm UTC](https://discourse.hibernate.org/t/how-to-use-collate-in-jpql/9542/2 "2024-05-20T15:04:13Z")

</div>

Hello, `collate()` is an Hibernate HQL custom function and is not supported by the JPA standard JPQL language.

The correct syntax is the first one you used: `COLLATE(i.displayId as display_id_collation)`. I’m not sure which other framework is causing the validation error, but `BadJpqlGrammarException` is not thrown by Hibernate. I suggest creating the query through the standard `org.hibernate.Session` APIs.

---

<div class="post-metadata">

**Author:** ![kevinm416](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/kevinm416/32/2892_2.png) [@kevinm416](https://discourse.hibernate.org/u/kevinm416)\
**Post date:** [May 27, 2024, 3:08pm UTC](https://discourse.hibernate.org/t/how-to-use-collate-in-jpql/9542/4 "2024-05-27T15:08:20Z")

</div>

Thank you! This worked for me:

```auto
List<Invoice> content =
        entityManager
            .createQuery(
                """
                  SELECT i
                    FROM Invoice i
                    WHERE (:customerId IS NULL OR i.customer.id = :customerId)
                    ORDER BY COLLATE(i.displayIdSearch as display_id_collation) ASC
                """,
                Invoice.class)
            .setParameter("customerId", filter.customerId())
            .getResultList();

```