CODEWITHSUNDEEP
sqldatabasehibernatejpamax
Vytsalo
Vytsalo

Reputation: 748

How to extract numbers(Integer) from String field in database and find the maximum

I have an entity. One field named "number" consists of String. It is a number + some text information. For example: 131-MOD 11853-ARO 983-AKK etc.

My task is: get the maximum of the first number. So, I have to extract Integer value from String "number" and find the maximum from it. For the examples higher, it would be the numbers 131, 11853 and 983. So, the maximum is 11853. I have to get this Integer value as a result. Here i have my try using Hibernate. But it working with only Integer values. How to extract number, i have no idea.

public Integer getMaxNumber()
{
   return (Integer) getSessionFactory().getCurrentSession().createQuery("select max(id) from EmployeeTripCard s").uniqueResult();
}

How can i do that?

Upvotes: 1

Views: 868

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522471

You may use the following JPQL query:

SELECT
    MAX(CAST(SUBSTRING(id, 1, LOCATE(id, '-') - 1) AS INTEGER))
FROM EmployeeTripCard s;

We can use LOCATE to find the index of the first -, then call SUBSTRING to find the initial number. Note carefully that we also need to cast this resulting string to an integer, in order for MAX to behave the way we want (numbers as text don't always sort the same way as actual pure numbers).

Upvotes: 1

Related Questions