Strings in JPQL and Criteria Queries

String values can appear in JPQL queries in several forms:

• As string literals, for example, 'abc' and ''.
• As parameters when string values are passed as query arguments.
• As path expressions that navigate to persistent string fields.
• As the results of predefined JPQL string manipulation functions.

LIKE - string pattern matching with wildcards

The [NOT] LIKE operator checks whether a string matches a pattern. The pattern can include ordinary characters and the following wildcard characters:

• The percent sign (%), which matches any sequence of zero or more characters.
• The underscore (_), which matches any single character.

The left operand is the string to check for a match (usually a path expression), and the right operand is 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 a literal underscore or percent sign, you must precede it with an escape character and specify that character in an ESCAPE clause. For example:

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

In the preceding expressions, the first percent sign (%) is a wildcard. The second, which follows the escape character, represents a literal % character.

LENGTH - counting characters in a string

The LENGTH(str) function returns the number of characters in the string argument 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 for a substring in a string and returns the substring's starting 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.

Positions are one-based (as in SQL), not zero-based (as in Java). Therefore, the position of the first character is 1. A value of zero (0) is returned if the substring is not found.

The optional third argument specifies the position from which to start the search.

LOWER and UPPER - changing string case

The LOWER(str) and UPPER(str) functions return a string that is converted to lowercase or uppercase, respectively.

For example:

• UPPER('Germany') is evaluated to 'GERMANY'.
• LOWER('Germany') is evaluated to 'germany'.

TRIM - removing leading and trailing characters

The TRIM([[LEADING|TRAILING|BOTH] [char] FROM] str) function returns a string after removing leading, trailing, or both types of characters (usually spaces).

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, spaces are removed, but you can specify a different character to remove:

• 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 - extracting a portion of a string

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

For example:

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

Positions are one-based (as in SQL), not zero-based (as in Java). If you do not specify the optional length argument, the function returns the remainder of the string, starting from the specified position.

Java string methods (ObjectDB extension)

ObjectDB also supports standard Java String methods.

For example:

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

The matches method of the String class is useful for pattern matching with regular expressions, which are more powerful than the LIKE operator.

Criteria query string expressions

Query String Expressions

The JPQL string operators and functions that are described above are also available as JPA criteria query expressions. The CriteriaBuilderjakarta.persistence.criteria.CriteriaBuilderUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides factory methods to build these expressions, as the following example shows:

  // Create path and parameter expressions:
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> path = country.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name");
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> param = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(String.class);
  // str [NOT] LIKE pattern
  Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. l1 = cb.likejakarta.persistence.criteria.CriteriaBuilder.like(Expression,Expression)Create a predicate for testing whether the expression satisfies the given pattern.(path, param);
  Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. l2 = cb.likejakarta.persistence.criteria.CriteriaBuilder.like(Expression,String)Create a predicate for testing whether the expression satisfies the given pattern.(path, "a%");
  Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. l3 = cb.notLikejakarta.persistence.criteria.CriteriaBuilder.notLike(Expression,Expression)Create a predicate for testing whether the expression does not satisfy the given pattern.(path, param);
  Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. l4 = cb.notLikejakarta.persistence.criteria.CriteriaBuilder.notLike(Expression,String)Create a predicate for testing whether the expression does not satisfy the given pattern.(path, "a%");
    // additional methods take also an escape character
  // LENGTH(str)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> length = cb.lengthjakarta.persistence.criteria.CriteriaBuilder.length(Expression)Create expression to return length of a string.(path);
  // LOCATE(str, substr [, start])
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> l1 = cb.locatejakarta.persistence.criteria.CriteriaBuilder.locate(Expression,Expression)Create expression to locate the position of one string within another, returning position of first character if found.(path, param);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> l2 = cb.locatejakarta.persistence.criteria.CriteriaBuilder.locate(Expression,String)Create expression to locate the position of one string within another, returning position of first character if found.(path, "x");
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> l3 = cb.locatejakarta.persistence.criteria.CriteriaBuilder.locate(Expression,Expression,Expression)Create expression to locate the position of one string within another, returning position of first character if found.(path, param, cb.literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal.(2));
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Integer> l4 = cb.locatejakarta.persistence.criteria.CriteriaBuilder.locate(Expression,String,int)Create 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)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> lower = cb.lowerjakarta.persistence.criteria.CriteriaBuilder.lower(Expression)Create expression for converting a string to lowercase.(path);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> upper = cb.upperjakarta.persistence.criteria.CriteriaBuilder.upper(Expression)Create expression for converting a string to uppercase.(param);
  // TRIM([[LEADING|TRAILING|BOTH] [char] FROM] str)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> t1 = cb.trimjakarta.persistence.criteria.CriteriaBuilder.trim(Expression)Create expression to trim blanks from both ends of a string.(path);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> t2 = cb.trimjakarta.persistence.criteria.CriteriaBuilder.trim(Expression,Expression)Create expression to trim character from both ends of a string.(literal(' '), path);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> t3 = cb.trimjakarta.persistence.criteria.CriteriaBuilder.trim(char,Expression)Create expression to trim character from both ends of a string.(' ', path);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> t4 = cb.trimjakarta.persistence.criteria.CriteriaBuilder.trim(Trimspec,Expression)Create expression to trim blanks from a string.(Trimspec.BOTH, path);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> t5 = cb.trimjakarta.persistence.criteria.CriteriaBuilder.trim(Trimspec,Expression,Expression)Create expression to trim character from a string.(Trimspec.LEADING, literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal.(' '), path);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> t6 = cb.trimjakarta.persistence.criteria.CriteriaBuilder.trim(Trimspec,char,Expression)Create expression to trim character from a string.(Trimspec.TRAILING, ' ', path);
  // CONCAT(str1, str2)
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> c1 = cb.concatjakarta.persistence.criteria.CriteriaBuilder.concat(Expression,Expression)Create an expression for string concatenation.(path, param);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> c2 = cb.concatjakarta.persistence.criteria.CriteriaBuilder.concat(Expression,String)Create an expression for string concatenation.(path, ".");
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> c3 = cb.concatjakarta.persistence.criteria.CriteriaBuilder.concat(String,Expression)Create an expression for string concatenation.("the", path);
  // SUBSTRING(str, pos [, length])
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> s1 = cb.substringjakarta.persistence.criteria.CriteriaBuilder.substring(Expression,Expression)Create an expression for substring extraction.(path, cb.literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal.(2));
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> s2 = cb.substringjakarta.persistence.criteria.CriteriaBuilder.substring(Expression,int)Create an expression for substring extraction.(path, 2);
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> s3 =
    cb.substringjakarta.persistence.criteria.CriteriaBuilder.substring(Expression,Expression,Expression)Create an expression for substring extraction.(path, cb.literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal.(2), cb.literaljakarta.persistence.criteria.CriteriaBuilder.literal(T)Create an expression for a literal.(3));
  Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<String> s4 = cb.substringjakarta.persistence.criteria.CriteriaBuilder.substring(Expression,int,int)Create an expression for substring extraction.(path, 2, 3);

As the preceding example demonstrates, most methods are overloaded to support optional arguments and to accept both simple Java objects and criteria expressions.