Question 90819 in PostgreSQL Question posted in
The official documentation can be found here.

I am looking to convert a select statement from oracle to postgres – Postgresql

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

Tags:

2

Answers


  1. 0

    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%'
    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search