JPQL: convert varchar to number

Question

How can i convert varchar to number in JPQL ???

I managed to do that in sql (Oracle) with this query:

SELECT Decode(Upper(column_name), Lower(column_name), To_Number(column_name), -1)
FROM table

I want to do this conversion with JPQL.

Thanks for your help

Solution

You can do the next:

CAST(e.salary AS NUMERIC(10,2))

Additionally, you can try:

• FUNC('TO_NUMBER', e.areaCode), FUNC allows for a database function to be call from JPQL.

• SQL('CAST(? AS CHAR(3))', e.areaCode), SQL allows for the usage and integration of SQL within JPQL.

Comments

[user3032749]

thanks for your help FUNC('TO_NUMBER', e.areaCode) works very well but there is an exception: I have java.sql.SQLException when i have for exemple '123TA' as value for e.areaCode in the database. I explain my objective: Data of my table in the database are: Column 2 5 AA 8 TT In JPQL i want to select the max of 'column' the result is 8

[Emmanuel Touzery]

Note that unless I'm wrong, FUNC() and SQL() are eclipselink specific. However FUNCTION() was introduced in JPA 2.1 (JEE7) which replaces FUNC() while being portable. Also see java-persistence-performance.blogspot.com/2012/05/…