Strings in JPQL and Criteria Queries

String values may appear in JPQL queries in various forms:

as string literals - e.g. 'abc', ''.
as parameters - when string values are assigned as arguments.
as path expressions - in navigation to persistent string fields.
as results of predefined JPQL string manipulation functions.

This page covers the following topics:

LIKE - String Pattern Matching with Wildcards

The [NOT] LIKE operator checks if a specified string matches a specified pattern. The pattern may include ordinary characters as well as the following wildcard characters:

The percent character (%) - which matches zero or more of any character.
The underscore character (_) - which matches any single character.

The left operand is always the string to check for a match (usually a path expression) and the right operand is always the pattern (usually a parameter or literal). For example:

c.name LIKE '_r%' is TRUE for 'Brazil' and FALSE for 'Denmark'

c.name LIKE '%' is always TRUE (for any c.name value).

c.name NOT LIKE '%' is always FALSE (for any c.name value).

To match an actual underscore or percent character it has to be preceded by an escape character, which is also specified. For example:

'100%' LIKE '%\%' ESCAPE '\' is evaluated to TRUE.
'100' LIKE '%\%' ESCAPE '\' is evaluated to FALSE.

In the expressions above only the first percent character (%) is a wildcard. The second (which appears after the escape character) represents a real % character.

LENGTH - Counting Characters in a String

The LENGTH(str) function returns the number of characters in the argument string as an int.

For example:

LENGTH('United States') is evaluated to 13.

LENGTH('China') is evaluated to 5.

LOCATE - Locating Substrings

The LOCATE(str, substr [, start]) function searches a substring and returns its position.

For example:

LOCATE('India', 'a') is evaluated to 5.
LOCATE('Japan', 'a', 3) is evaluated to 4.

LOCATE('Mexico', 'a') is evaluated to 0.

Notice that positions are one-based (as in SQL) rather than zero-based (as in Java). Therefore, the position of the first character is 1. Zero (0) is returned if the substring is not found.

The third argument (when present) specifies from which position to start the search.

LOWER and UPPER - Changing String Case

The LOWER(str) and UPPER(str) functions return a string after conversion to lowercase or uppercase (respectively).

For example:

UPPER('Germany') is evaluated to 'GERMANY'.

LOWER('Germany') is evaluated to 'germany'.

TRIM - Stripping Leading and Trailing Characters

The TRIM([[LEADING|TRAILING|BOTH] [char] FROM] str) function returns a string after removing leading and/or trailing characters (usually space characters).

For example:

TRIM(' UK ') is evaluated to 'UK'.

TRIM(LEADING FROM ' UK ') is evaluated to 'UK '.

TRIM(TRAILING FROM ' UK ') is evaluated to ' UK'.

TRIM(BOTH FROM ' UK ') is evaluated to 'UK'.

By default, space characters are removed, but any other character can also be specified:

TRIM('A' FROM 'ARGENTINA') is evaluated to 'RGENTIN.

TRIM(LEADING 'A' FROM 'ARGENTINA') is evaluated to 'RGENTINA'.

TRIM(TRAILING 'A' FROM 'ARGENTINA') is evaluated to 'ARGENTIN'.

CONCAT - String Concatenation

The CONCAT(str1, str2, ...) function returns the concatenation of the specified strings.

For example:

CONCAT('Serbia', ' and ', 'Montenegro') is evaluated to 'Serbia and Montenegro'.

SUBSTRING - Getting a Portion of a String

The SUBSTRING(str, pos [, length]) function returns a substring of a specified string.

For example:

SUBSTRING('Italy', 3) is evaluated to 'aly'.
SUBSTRING('Italy', 3, 2) is evaluated to 'al'.

Notice that positions are one-based (as in SQL) rather than zero-based (as in Java). If length is not specified (the third optional argument), the entire string suffix, starting at the specified position, is returned.

Java String Methods (ObjectDB Extension)

ObjectDB also supports ordinary Java String methods.

For example:

'Canada'.length() is evaluated to 6.
'Poland'.toLowerCase() is evaluated to 'poland'.

The matches method of the String class can be useful when there is a need for pattern matching using regular expressions (which are more powerful than the LIKE operator).

Criteria Query String Expressions

JPQL string operators and functions (which are described above) are available also as JPA criteria query expressions. The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilder - JPA InterfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides factory methods for building these expressions, as shown in the following examples:

  // Create path and parameter expressions:
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> path = country.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("name");
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> param = cb.parameterCriteriaBuilder.parameter(paramClass) - JPA MethodCreate a parameter expression.(String.class);

  // str [NOT] LIKE pattern
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. l1 = cb.likeCriteriaBuilder.like(x,pattern) - JPA MethodCreate a predicate for testing whether the expression
 satisfies the given pattern.(path, param);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. l2 = cb.likeCriteriaBuilder.like(x,pattern) - JPA MethodCreate a predicate for testing whether the expression
 satisfies the given pattern.(path, "a%");
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. l3 = cb.notLikeCriteriaBuilder.notLike(x,pattern) - JPA MethodCreate a predicate for testing whether the expression
 does not satisfy the given pattern.(path, param);
  Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions. l4 = cb.notLikeCriteriaBuilder.notLike(x,pattern) - JPA MethodCreate a predicate for testing whether the expression
 does not satisfy the given pattern.(path, "a%");
    // additional methods take also an escape character

  // LENGTH(str)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> length = cb.lengthCriteriaBuilder.length(x) - JPA MethodCreate expression to return length of a string.(path);

  // LOCATE(str, substr [, start])
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> l1 = cb.locateCriteriaBuilder.locate(x,pattern) - JPA MethodCreate expression to locate the position of one string
 within another, returning position of first character
 if found.(path, param);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> l2 = cb.locateCriteriaBuilder.locate(x,pattern) - JPA MethodCreate expression to locate the position of one string
 within another, returning position of first character
 if found.(path, "x");
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> l3 = cb.locateCriteriaBuilder.locate(x,pattern,from) - JPA MethodCreate expression to locate the position of one string
 within another, returning position of first character
 if found.(path, param, cb.literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal.(2));
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Integer> l4 = cb.locateCriteriaBuilder.locate(x,pattern,from) - JPA MethodCreate expression to locate the position of one string
 within another, returning position of first character
 if found.(path, "x", 2);

  // LOWER(str) and UPPER(str)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> lower = cb.lowerCriteriaBuilder.lower(x) - JPA MethodCreate expression for converting a string to lowercase.(path);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> upper = cb.upperCriteriaBuilder.upper(x) - JPA MethodCreate expression for converting a string to uppercase.(param);

  // TRIM([[LEADING|TRAILING|BOTH] [char] FROM] str)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> t1 = cb.trimCriteriaBuilder.trim(x) - JPA MethodCreate expression to trim blanks from both ends of
 a string.(path);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> t2 = cb.trimCriteriaBuilder.trim(t,x) - JPA MethodCreate expression to trim character from both ends of
 a string.(literal(' '), path);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> t3 = cb.trimCriteriaBuilder.trim(t,x) - JPA MethodCreate expression to trim character from both ends of
 a string.(' ', path);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> t4 = cb.trimCriteriaBuilder.trim(ts,x) - JPA MethodCreate expression to trim blanks from a string.(Trimspec.BOTH, path);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> t5 = cb.trimCriteriaBuilder.trim(ts,t,x) - JPA MethodCreate expression to trim character from a string.(Trimspec.LEADING, literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal.(' '), path);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> t6 = cb.trimCriteriaBuilder.trim(ts,t,x) - JPA MethodCreate expression to trim character from a string.(Trimspec.TRAILING, ' ', path);

  // CONCAT(str1, str2)
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> c1 = cb.concatCriteriaBuilder.concat(x,y) - JPA MethodCreate an expression for string concatenation.(path, param);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> c2 = cb.concatCriteriaBuilder.concat(x,y) - JPA MethodCreate an expression for string concatenation.(path, ".");
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> c3 = cb.concatCriteriaBuilder.concat(x,y) - JPA MethodCreate an expression for string concatenation.("the", path);

  // SUBSTRING(str, pos [, length])
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> s1 = cb.substringCriteriaBuilder.substring(x,from) - JPA MethodCreate an expression for substring extraction.(path, cb.literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal.(2));
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> s2 = cb.substringCriteriaBuilder.substring(x,from) - JPA MethodCreate an expression for substring extraction.(path, 2);
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> s3 =
    cb.substringCriteriaBuilder.substring(x,from,len) - JPA MethodCreate an expression for substring extraction.(path, cb.literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal.(2), cb.literalCriteriaBuilder.literal(value) - JPA MethodCreate an expression for a literal.(3));
  Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<String> s4 = cb.substringCriteriaBuilder.substring(x,from,len) - JPA MethodCreate an expression for substring extraction.(path, 2, 3);

As demonstrated above, most methods are overloaded in order to support optional arguments and when applicable simple Java objects as well as criteria expressions.

< Numbers in JPQL ^ Query Expressions Date and Time in JPQL >