I am looking to convert a select statement from oracle to postgres-CodePudding

select max(to_number(ltrim(aefo_number,'VE')))
from   exemption
where  aefo_number like 'E%'
or     aefo_number like 'V%'

I am getting Function to_number(text) does not exist error for the above select statement and I am unable to convert it.

If anyone know the syntax for the select statement please let me know

CodePudding user response：

Unlike Oracle, Postgres' to_number() function always needs a format mask. So you would need something like to_number(ltrim(aefo_number,'VE'), '99999999999')

If you don't want to (or can't) specify a format mask, you could cast the value to a numeric oder integer:

select max(ltrim(aefo_number,'VE')::integer)
from   exemption
where  aefo_number like 'E%'
or     aefo_number like 'V%'

CodePudding user response：

If V and E are only the first character then you can use:

select MAX(SUBSTR(aefo_number,2)::integer)
from   exemption
where  aefo_number like 'E%'
or     aefo_number like 'V%'

fiddle