JPQL functions cheat sheet

java | Thursday, 21 August 2025

Here’s a concise JPQL functions cheat sheet with examples you can use directly in your queries:

1. String Functions

Function	Description	Example
`CONCAT(str1, str2, ...)`	Concatenates strings	`SELECT CONCAT(e.firstName, ' ', e.lastName) FROM Employee e`
`SUBSTRING(str, start, length)`	Extracts substring	`SUBSTRING(e.name, 1, 3)`
`TRIM(str)`	Removes leading/trailing spaces	`TRIM(e.name)`
`LOWER(str)`	Converts to lowercase	`LOWER(e.name)`
`UPPER(str)`	Converts to uppercase	`UPPER(e.name)`
`LENGTH(str)`	Returns length of string	`LENGTH(e.name)`
`LOCATE(substr, str)`	Position of substring	`LOCATE('Smith', e.name)`

Function	Description	Example
`ABS(number)`	Absolute value	`ABS(e.salary)`
`SQRT(number)`	Square root	`SQRT(e.salary)`
`MOD(number, divisor)`	Modulus	`MOD(e.salary, 1000)`
`SIZE(collection)`	Number of elements in a collection	`SIZE(e.projects)`

Function	Description	Example
`CURRENT_DATE`	Current date	`WHERE e.hireDate = CURRENT_DATE`
`CURRENT_TIME`	Current time	`CURRENT_TIME`
`CURRENT_TIMESTAMP`	Current date and time	`CURRENT_TIMESTAMP`

sql
SELECT e.name,
       CASE WHEN e.salary > 5000 THEN 'High' ELSE 'Low' END
FROM Employee e

If your JPA provider supports FUNCTION:

java
SELECT FUNCTION('DATE_FORMAT', e.hireDate, '%Y-%m-%d') FROM Employee e
SELECT FUNCTION('NOW') FROM Employee e

This lets you access functions not natively supported in JPQL (like window functions, GROUP_CONCAT, etc.).