Close

JPQL String Functions

[Updated: Jun 8, 2018, Created: Jun 7, 2018]

JPQL provides following built-in string functions:

  • CONCAT(string, string, .... more strings)

    concatenates two or more strings into one string.
    CONCAT('one', '-', 'two') returns one-two

  • SUBSTRING(string, startIndex, length)

    The second and third arguments of the SUBSTRING function denote the starting position and length of the substring to be returned. These arguments are integers. The third argument is optional, if it is not specified, the substring from the start position to the end of the string is returned.
    SUBSTRING('world', 3) returns rld
    SUBSTRING('world', 3, 2) returns rl
    SUBSTRING('world', 1, 3) returns wor

  • TRIM([[LEADING|TRAILING|BOTH][char] FROM] string)

    The TRIM function trims the specified character from a string.
    The keywords LEADING, TRAILING, BOTH are all optional, if not specified BOTH is assumed.
    If the 'char' to be trimmed is not specified, it will be assumed to be space (or blank).
    TRIM(' world ') returns world
    TRIM(TRAILING '*' FROM 'world****') returns world
    TRIM(BOTH '*' FROM '***world****') returns world

  • UPPER(string)

    It converts a string to upper case.
    UPPER('world') returns WORLD

  • LOWER(string)

    It converts a string to lower case.
    LOWER('World') returns world

  • LENGTH(string)

    It returns the length of a string as an integer.
    LENGTH('world') returns 5
    LENGTH('') returns 0

  • LOCATE(searchString, inputString, position)

    It returns the position of a given string within a string, starting the search at an optional specified position.
    It returns the first position at which the string was found as an integer.
    The first argument is the string to be located; the second argument is the string to be searched; the optional third argument is an integer that represents the string position at which the search is started (by default, the beginning of the string to be searched). The first position in a string is denoted by 1. If the string is not found, 0 is returned.
    LOCATE('a', 'banana') returns 2
    LOCATE('a', 'banana', 3) returns 4
    LOCATE('z', 'banana') returns 0

Example

Entity

@Entity
public class Employee {
  @Id
  @GeneratedValue
  private long id;
  private String firstName;
  private String lastName;
  private String dept;
  private String address;
    .............
}

Using string functions

public class ExampleMain {
  private static EntityManagerFactory entityManagerFactory =
          Persistence.createEntityManagerFactory("example-unit");

  public static void main(String[] args) {
      try {
          persistEmployees();
          findEmployeeFullName();
          findLongestNamedEmployee();
          findAllCities();
          findByCity();
          findByDept();
      } finally {
          entityManagerFactory.close();
      }
  }

  public static void persistEmployees() {
      Employee employee1 = Employee.create("Diana", "John", "IT", "111 Round Drive, Sunland");
      Employee employee2 = Employee.create("Rose", "Burgess", "Admin", "2623  Elmwood Avenue, Scottsdale");
      Employee employee3 = Employee.create("Denise", "Clarkson", "admin", "4000 Johnny Lane, Mars Town");
      Employee employee4 = Employee.create("Mike", "Young", "IT", "323 Tik Tok Road, Hiland");
      Employee employee5 = Employee.create("Linda", "McDonald", "Sales", "2000 Wet Road, Sunland");
      EntityManager em = entityManagerFactory.createEntityManager();
      em.getTransaction().begin();
      em.persist(employee1);
      em.persist(employee2);
      em.persist(employee3);
      em.persist(employee4);
      em.persist(employee5);
      em.getTransaction().commit();

      System.out.println("-- Employees persisted --");
      Query query = em.createQuery(
              "SELECT e FROM Employee e");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findEmployeeFullName() {
      System.out.println("-- Employees full names --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT CONCAT(e.firstName, ' ', e.lastName)  FROM Employee e");
      List<String> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findLongestNamedEmployee() {
      System.out.println("-- Find employee having the longest full name name --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e FROM Employee e where "
                      + "LENGTH(CONCAT(e.firstName, ' ', e.lastName)) = "
                      + "(SELECT MAX(LENGTH(CONCAT(e2.firstName, ' ', e2.lastName))) FROM  Employee e2)");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }


  private static void findAllCities() {
      System.out.println("-- All Cities --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT DISTINCT TRIM(SUBSTRING(e.address, LOCATE(',', e.address)+1)) as city"
                      + " FROM Employee e order by city");
      List<String> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findByCity() {
      System.out.println("-- Find By City 'Sunland' --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e FROM Employee e WHERE TRIM(SUBSTRING(e.address, LOCATE(',', e.address)+1))='Sunland'");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }

  private static void findByDept() {
      System.out.println("-- Find By Dept 'Admin' --");
      EntityManager em = entityManagerFactory.createEntityManager();
      Query query = em.createQuery(
              "SELECT e FROM Employee e WHERE LOWER(e.dept)='admin'");
      List<Employee> resultList = query.getResultList();
      resultList.forEach(System.out::println);
      em.close();
  }
}
-- Employees persisted --
Employee{id=1, firstName='Diana', lastName='John', dept='IT', address='111 Round Drive, Sunland'}
Employee{id=2, firstName='Rose', lastName='Burgess', dept='Admin', address='2623 Elmwood Avenue, Scottsdale'}
Employee{id=3, firstName='Denise', lastName='Clarkson', dept='admin', address='4000 Johnny Lane, Mars Town'}
Employee{id=4, firstName='Mike', lastName='Young', dept='IT', address='323 Tik Tok Road, Hiland'}
Employee{id=5, firstName='Linda', lastName='McDonald', dept='Sales', address='2000 Wet Road, Sunland'}
-- Employees full names --
Diana John
Rose Burgess
Denise Clarkson
Mike Young
Linda McDonald
-- Find employee having the longest full name name --
Employee{id=3, firstName='Denise', lastName='Clarkson', dept='admin', address='4000 Johnny Lane, Mars Town'}
-- All Cities --
Hiland
Mars Town
Scottsdale
Sunland
-- Find By City 'Sunland' --
Employee{id=1, firstName='Diana', lastName='John', dept='IT', address='111 Round Drive, Sunland'}
Employee{id=5, firstName='Linda', lastName='McDonald', dept='Sales', address='2000 Wet Road, Sunland'}
-- Find By Dept 'Admin' --
Employee{id=2, firstName='Rose', lastName='Burgess', dept='Admin', address='2623 Elmwood Avenue, Scottsdale'}
Employee{id=3, firstName='Denise', lastName='Clarkson', dept='admin', address='4000 Johnny Lane, Mars Town'}

Example Project

Dependencies and Technologies Used:

  • h2 1.4.197: H2 Database Engine.
  • hibernate-core 5.2.13.Final: The core O/RM functionality as provided by Hibernate.
  • JDK 1.8
  • Maven 3.3.9

JPQL String Functions Select All Download
  • jpql-string-functions
    • src
      • main
        • java
          • com
            • logicbig
              • example
        • resources
          • META-INF

See Also