JPA Hibernate Order by alphanumeric field

Question

I am using Jpa/Hibernate to access MariaDB in a Spring Boot application

I am strugling to sort data by an alphanumeric field containng numbers that might end with one letter (pattern \d+[a-z]?)

e.g.

• 10
• 104
• 20a
• 100b

and I need them ordered like this

• 10
• 20a
• 100b
• 104

I a bulding my own query with the Criteria Api because I also have to do some complex filtering.

@Transactional(readOnly = true)
public class EntryRepositoryImpl implements EntryRepositoryCustom {

  @PersistenceContext
  private EntityManager entityManager;

  @Override
  public Page<Entry> get(MultiValueMap<String, String> parameters, Pageable pageable) {

    CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

    //  count total number of filterd entries
    Long totalResultCount = getResultCount(parameters, criteriaBuilder);

    // build query to get filterd entries
    CriteriaQuery<Entry> selectQuery = criteriaBuilder.createQuery(Entry.class);
    Root<Entry> getRoot = selectQuery.from(Entry.class);
    CriteriaQuery<Entry> select = selectQuery.select(getRoot);

    addFilters(parameters, criteriaBuilder, getRoot, select);

    // add sorting
    List<javax.persistence.criteria.Order> sortOrders = JpaUtils.translateSorting(pageable,
        getRoot);
    select.orderBy(sortOrders);

    //  get one page of filterd entries
    List<Entry> results = getPageResults(pageable, select);
    return new PageImpl<>(results, pageable, totalResultCount);
  }

  /**
   * Translate spring to jpa sorting.
   */
  public static List<javax.persistence.criteria.Order> translateSorting(Pageable pageable,
      Root<Entry> root) {
    List<Sort.Order> orders = new ArrayList<>();
    if (pageable.getSort() != null) {
      pageable.getSort().iterator().forEachRemaining(orders::add);
    }

    return orders.stream().
        map(order -> {
          String[] parts = order.getProperty().split("\\.");
          String field = parts[0];
          Path path = parts.length == 2 ? root.join(field).get(parts[1]) : root.get(field);
          return new OrderImpl(path, order.isAscending());
        })
        .collect(Collectors.toList());
  }

I already have a custom comparator but it seems, there is no way to translate it so the DB could use it.

So far I found the following solutions/ideas

• using @SortComparator, but it is not feasible for my use case because the ordering has to happen in the database, because there are over 500k complex rows.
• this sql base solution but don't know how to translate it into the Criteria Api.
• after looking at the function of CriteriaBuilder (javadoc) I got the idea to split the value into the numeric and string parts and apply to orders but there is not function to split with a regular expression.

Edit:

For now I did split the field into 2 and use two sort expression.