/ /

Use String Functions in Queries

Overview

In this guide, you can learn how to call Hibernate Query Language (HQL) string functions on a string field by using the MongoDB Extension for Hibernate ORM. String functions let you measure, reformat, and combine string values as part of a query.

You can call each function in a SELECT clause. The Hibernate ORM extension translates each function call to a MongoDB aggregation expression in the $project stage. Because the Hibernate ORM extension does not support function calls as operands of a computed expression, you cannot combine a string function with an arithmetic operator.

The Hibernate ORM extension supports the following string functions:

Function	MongoDB Translation
`length()`, `character_length()`, `char_length()`	$strLenCP
`upper()`	$toUpper
`lower()`	$toLower
`concat()`, `\|\|`	$concat
`substring()`	$substrCP
`locate()`	$indexOfCP
`replace()`	$replaceAll
`trim()`	$trim, $ltrim, or $rtrim, depending on the specified trim direction
`pad()`, `lpad()`, `rpad()`	Computed from $strLenCP, $substrCP, and $concat
`repeat()`	Computed from $range and $reduce

Note

Unsupported String Functions

The Hibernate ORM extension does not support the HQL position(), overlay(), left(), right(), and collate() string functions. A query that calls one of these functions throws a FeatureNotSupportedException.

To use an unsupported string function, call the createNativeQuery() method and include the relevant operator in your MongoDB Query Language statement. To learn more, see the Perform Native Database Queries guide.

Sample Data

The examples in this guide use the Movie entity, which represents the sample_mflix.movies collection from the Atlas sample datasets. The Movie entity has the following definition:

import com.mongodb.hibernate.annotations.ObjectIdGenerator;
import org.bson.types.ObjectId;
import java.time.Instant;
import java.util.List;
import jakarta.persistence.Embedded;
import jakarta.persistence.Entity;
import jakarta.persistence.FetchType;
import jakarta.persistence.Id;
import jakarta.persistence.OneToMany;
import jakarta.persistence.Table;
@Entity
@Table(name = "movies")
public class Movie {
   @Id
   @ObjectIdGenerator
   private ObjectId id;
   private String title;
   private String plot;
   private int year;
   private List<String> cast;
   private List<String> directors;
   private Instant released;
   @Embedded
   private Awards awards;
   @OneToMany(mappedBy = "movie", fetch = FetchType.LAZY)
   private List<Comment> comments;
   public Movie(String title, String plot, int year, List<String> cast, List<String> directors, Instant released, Awards awards) {
      this.title = title;
      this.plot = plot;
      this.year = year;
      this.cast = cast;
      this.directors = directors;
      this.released = released;
      this.awards = awards;
   }
   public Movie() {
   }
   public ObjectId getId() {
      return id;
   }
   public String getTitle() {
      return title;
   }
   public void setTitle(String title) {
      this.title = title;
   }
   public String getPlot() {
      return plot;
   }
   public void setPlot(String plot) {
      this.plot = plot;
   }
   public int getYear() {
      return year;
   }
   public void setYear(int year) {
      this.year = year;
   }
   public List<String> getCast() {
      return cast;
   }
   public void setCast(List<String> cast) {
      this.cast = cast;
   }
   public List<String> getDirectors() {
      return directors;
   }
   public void setDirectors(List<String> directors) {
      this.directors = directors;
   }
   public Awards getAwards() {
      return awards;
   }
   public void setAwards(Awards awards) {
      this.awards = awards;
   }
   public Instant getReleased() {
      return released;
   }
   public void setReleased(Instant released) {
      this.released = released;
   }
   public List<Comment> getComments() {
      return comments;
   }
   public void setComments(List<Comment> comments) {
      this.comments = comments;
   }
}

To learn how to create a Java application that uses the MongoDB Extension for Hibernate ORM to interact with this MongoDB sample collection, see the Get Started tutorial.

The examples in this guide use the Movie entity's title field, which stores a String value.

Measure the Length of a String

The length(str) function returns the number of characters in a string. You can also call this function as character_length(str) or char_length(str), which are aliases for the same function. Because the Hibernate ORM extension translates the call to the $strLenCP operator, the function counts Unicode code points rather than bytes.

The following example returns the title of the "The Hunger Games" movie in the sample_mflix.movies collection and the length of the title:

var lengthResult = session.createQuery(
                "select title, length(title) as titleLength from Movie where title = :title",
                Object[].class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var row : lengthResult) {
    System.out.println("Title: " + row[0] + ", Length: " + row[1]);
}

var lengthResult = entityManager.createQuery(
                "select m.title, length(m.title) as titleLength from Movie m where m.title = :title",
                Object[].class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var row : lengthResult) {
    System.out.println("Title: " + row[0] + ", Length: " + row[1]);
}

The Hibernate ORM extension translates the preceding query to the following $project stage:

{
  "$project": {
    "title": true,
    "titleLength": {
      "$strLenCP": "$title"
    },
    "_id": 0
  }
}

Change the Case of a String

The upper(str) and lower(str) functions convert a string to uppercase or lowercase.

The following example returns the title of the "The Hunger Games" movie in uppercase:

var upperResult = session.createQuery(
                "select upper(title) as upperTitle from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var upperTitle : upperResult) {
    System.out.println("Uppercase Title: " + upperTitle);
}

var upperResult = entityManager.createQuery(
                "select upper(m.title) as upperTitle from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var upperTitle : upperResult) {
    System.out.println("Uppercase Title: " + upperTitle);
}

The Hibernate ORM extension translates the preceding query to the following $project stage:

{
  "$project": {
    "upperTitle": {
      "$toUpper": "$title"
    },
    "_id": 0
  }
}

Concatenate Strings

The concat(x, y) function joins two or more values into a single string. The Hibernate ORM extension converts each operand to a string before joining, so you can concatenate numeric values or concatenate a numeric value to a string value.

The following example appends an exclamation point to the title of the "The Hunger Games" movie:

var concatResult = session.createQuery(
                "select concat(title, '!') as excitedTitle from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var excitedTitle : concatResult) {
    System.out.println("Concatenated Title: " + excitedTitle);
}

var concatResult = entityManager.createQuery(
                "select concat(m.title, '!') as excitedTitle from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var excitedTitle : concatResult) {
    System.out.println("Concatenated Title: " + excitedTitle);
}

Tip

You can also use the double pipe operator (||) to join two values. The following query is equivalent to the preceding example:

"select title || '!' as excitedTitle from Movie where title = :title"

The Hibernate ORM extension translates the preceding query to the following $project stage:

{
  "$project": {
    "excitedTitle": {
      "$concat": [
        { "$toString": "$title" },
        { "$toString": "!" }
      ]
    },
    "_id": 0
  }
}

Extract a Substring

The substring(str, start) function returns the part of a string that begins at the start position. The start position is one-based, so the first character in the string is at position 1. To limit the result to a fixed number of characters, call the function as substring(str, start, length).

If you pass a start position of 0 or a negative value, the Hibernate ORM extension reads from the beginning of the string. If you also pass a length, the extension subtracts the characters before the first position from the returned length. For example, substring(str, -1, 3) returns one character, and substring(str, 0, 3) returns two characters.

The following example returns the first six characters of the title of the "The Hunger Games" movie:

var substringResult = session.createQuery(
                "select substring(title, 1, 6) as titlePrefix from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var titlePrefix : substringResult) {
    System.out.println("Title Prefix: " + titlePrefix);
}

var substringResult = entityManager.createQuery(
                "select substring(m.title, 1, 6) as titlePrefix from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var titlePrefix : substringResult) {
    System.out.println("Title Prefix: " + titlePrefix);
}

Locate a Substring

The locate(pattern, str) function returns the one-based position of the first occurrence of pattern in the string, or 0 if the string does not contain the pattern.

To begin the search at a specific position, call the function as locate(pattern, str, start). Because the start position is one-based, the first character in the string is at position 1. A start value of 0 or a negative number starts the search at position 1.

The following example returns the position of the substring "Hunger" in the title of the "The Hunger Games" movie:

var locateResult = session.createQuery(
                "select locate('Hunger', title) as position from Movie where title = :title",
                Integer.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var position : locateResult) {
    System.out.println("Position: " + position);
}

var locateResult = entityManager.createQuery(
                "select locate('Hunger', m.title) as position from Movie m where m.title = :title",
                Integer.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var position : locateResult) {
    System.out.println("Position: " + position);
}

Replace a Substring

The replace(str, pattern, replacement) function replaces every occurrence of pattern in the string with replacement. The Hibernate ORM extension translates the call to the $replaceAll operator. All three arguments must be strings.

The following example replaces the substring "Hunger" with "Video" in the title of the "The Hunger Games" movie:

var replaceResult = session.createQuery(
                "select replace(title, 'Hunger', 'Video') as newTitle from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var newTitle : replaceResult) {
    System.out.println("New Title: " + newTitle);
}

var replaceResult = entityManager.createQuery(
                "select replace(m.title, 'Hunger', 'Video') as newTitle from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var newTitle : replaceResult) {
    System.out.println("New Title: " + newTitle);
}

Trim Characters from a String

The trim(str) function removes leading and trailing spaces from a string. To remove characters from only one end of the string, use trim(leading char from str) or trim(trailing char from str). To remove a character other than a space from both ends of the string, specify it by using the both and from keywords, as in trim(both char from str).

The following example removes the leading "T" character from the title of the "The Hunger Games" movie:

var trimResult = session.createQuery(
                "select trim(leading 'T' from title) as trimmedTitle from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var trimmedTitle : trimResult) {
    System.out.println("Trimmed Title: " + trimmedTitle);
}

var trimResult = entityManager.createQuery(
                "select trim(leading 'T' from m.title) as trimmedTitle from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var trimmedTitle : trimResult) {
    System.out.println("Trimmed Title: " + trimmedTitle);
}

The Hibernate ORM extension translates the preceding query to the following $project stage:

{
  "$project": {
    "trimmedTitle": {
      "$ltrim": {
        "chars": "T",
        "input": "$title"
      }
    },
    "_id": 0
  }
}

Pad a String

The pad(str with length leading) and pad(str with length trailing) functions extend a string to the specified length by adding spaces to the start or end of the string str. To use a character other than a space, specify the character after the direction, as in pad(str with 20 leading char).

You can also call the lpad(str, length, padding) and rpad(str, length, padding) functions, which pad a string with a value that can contain more than one character. The Hibernate ORM extension repeats the padding value as many times as needed and truncates it to reach the target length. For example, lpad('x', 6, 'ab') returns "ababax".

Padding behaves as follows in these cases:

If the target length is shorter than the string, the Hibernate ORM extension truncates the string to the target length by removing the trailing characters after the target length.
If the target length is negative, the function returns an empty string.
If the padding value is an empty string, the function returns the original string.

The following example uses leading asterisks to pad the title of the "The Hunger Games" movie to 20 characters:

var padResult = session.createQuery(
                "select pad(title with 20 leading '*') as paddedTitle from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var paddedTitle : padResult) {
    System.out.println("Padded Title: " + paddedTitle);
}

var padResult = entityManager.createQuery(
                "select pad(m.title with 20 leading '*') as paddedTitle from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var paddedTitle : padResult) {
    System.out.println("Padded Title: " + paddedTitle);
}

Repeat a String

The repeat(str, count) function returns a string that contains count copies of str.

The following example returns the title of the "The Hunger Games" movie, repeated twice:

var repeatResult = session.createQuery(
                "select repeat(title, 2) as repeatedTitle from Movie where title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var repeatedTitle : repeatResult) {
    System.out.println("Repeated Title: " + repeatedTitle);
}

Repeated Title: The Hunger GamesThe Hunger Games

var repeatResult = entityManager.createQuery(
                "select repeat(m.title, 2) as repeatedTitle from Movie m where m.title = :title",
                String.class)
        .setParameter("title", "The Hunger Games")
        .getResultList();
for (var repeatedTitle : repeatResult) {
    System.out.println("Repeated Title: " + repeatedTitle);
}

Repeated Title: The Hunger GamesThe Hunger Games

Additional Information

To learn more about creating query filters and using operators in your query statements, see the Specify a Query guide.

To learn how to call datetime functions in a query, see the Use Datetime Functions in Queries guide.

To learn more about using HQL and JPQL to run queries, see A Guide to Hibernate Query Language in the Hibernate ORM documentation.

Back

Datetime Functions in Queries

Join Entities Across Collections