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 thestringargument starting atstartIndex(1-based) and optionally ending atlengthcharacters paststartIndex. If thelengthargument is not specified, the substring from thestartIndexto the end of thestringis 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 ofsearchStringincandidateString. 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 numbereraised 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 (basee) 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 ofnumberanddivisor.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 numberSELECT 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,
studentWaitlistis 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_fieldFROMdate_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_partFROMdate_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' }