2.10.1.5. JPQL Functions

As well as supporting direct field and relation comparisons, JPQL supports a pre-defined set of functions that you can apply.

  • CONCAT(string1, string2): Concatenates two string fields or literals. For example:

    SELECT x FROM Magazine x WHERE CONCAT(x.title, 's') = 'JDJs'
  • SUBSTRING(string, startIndex, [length]): Returns the part of the string argument starting at startIndex (1-based) and optionally ending at length characters past startIndex. If the length argument is not specified, the substring from the startIndex to the end of the string is returned.

    SELECT x FROM Magazine x WHERE SUBSTRING(x.title, 1, 1) = 'J'
  • TRIM([LEADING | TRAILING | BOTH] [character FROM] string: Trims the specified character from either the beginning ( LEADING) end ( TRAILING) or both ( BOTH) of the string argument. If no trim character is specified, the space character will be trimmed.

    SELECT x FROM Magazine x WHERE TRIM(BOTH 'J' FROM x.title) = 'D'
  • LOWER(string): Returns the lower-case of the specified string argument.

    SELECT x FROM Magazine x WHERE LOWER(x.title) = 'jdj'
  • UPPER(string): Returns the upper-case of the specified string argument.

    SELECT x FROM Magazine x WHERE UPPER(x.title) = 'JAVAPRO'
  • LENGTH(string): Returns the number of characters in the specified string argument.

    SELECT x FROM Magazine x WHERE LENGTH(x.title) = 3
  • LOCATE(searchString, candidateString [, startIndex]): Returns the first index of searchString in candidateString. Positions are 1-based. If the string is not found, returns 0.

    SELECT x FROM Magazine x WHERE LOCATE('D', x.title) = 2
  • ABS(number): Returns the absolute value of the argument.

    SELECT x FROM Magazine x WHERE ABS(x.price) >= 5.00
  • CEILING(number): Returns the smallest (closest to negative infinity) value that is greater than or equal to the argument and is equal to a mathematical integer.

    SELECT x FROM Magazine x WHERE CEILING(x.price) >= 10
  • EXP(number): Returns the Euler’s number e raised to the power of a value.

    SELECT x FROM Magazine x WHERE EXP(x.price) >= 10
  • FLOOR(number): Returns the largest (closest to positive infinity) value that is less than or equal to the argument and is equal to a mathematical integer.

    SELECT x FROM Magazine x WHERE FLOOR(x.price) >= 10
  • LN(number): Returns the natural logarithm (base e) of a value.

    SELECT x FROM Magazine x WHERE LN(x.price) >= 10
  • SQRT(number): Returns the square root of the argument.

    SELECT x FROM Magazine x WHERE SQRT(x.price) >= 1.00
  • MOD(number, divisor): Returns the modulo of number and divisor.

    SELECT x FROM Magazine x WHERE MOD(x.price, 10) = 0
  • POWER(base, exponent): Returns the value of the first argument raised to the power of the second argument.

    SELECT x FROM Magazine x WHERE POWER(x.price, 2) >= 10
  • ROUND(number, precision): Rounds the number to given precision.

    SELECT x FROM Magazine x WHERE ROUND(x.price, 2) >= 10
  • SIGN(number): Returns the sign (-1, 0, 1) of a number

    SELECT x FROM Magazine x WHERE SIGN(x.price) >= 0
  • INDEX(identification_variable): Returns an integer value corresponding to the position of its argument in an ordered list. The INDEX function can only be applied to identification variables denoting types for which an order column has been specified.

    In the following example, studentWaitlist is a list of students for which an order column has been specified, the query returns the name of the first student on the waiting list of the course named 'Calculus':

    SELECT w.name FROM Course c JOIN c.studentWaitlist w WHERE c.name = ‘Calculus’ AND INDEX(w) = 0
  • CURRENT_DATE: Returns the current date.

  • CURRENT_TIME: Returns the current time.

  • CURRENT_TIMESTAMP: Returns the current timestamp.

  • LOCAL DATE: returns the value of current date on the database server.

  • LOCAL TIME: returns the value of current time on the database server.

  • LOCAL DATETIME: returns the value of current timestamp on the database server.

  • EXTRACT(date_field FROM date_expression) : extracts a given field (year, quarter, month, week, day, hour, minute or second) from a datetime.

    SELECT c FROM Customer AS c WHERE EXTRACT(YEAR FROM c.birthDate)  (EXTRACT(YEAR FROM CURRENT_DATE) - 13)
  • EXTRACT(date_part FROM date_expression) : extracts a date part (date or time) from a datetime.

    SELECT c FROM Customer AS c WHERE EXTRACT(DATE FROM c.createdTimestamp)  { d '1969-07-20' }